System Stored Procedures Tips

Microsoft provides a good set of basic queries into their system tables. However, some of these procedures lack some additional information that I am interested in and I find myself constantly issuing queries to get this additional data. Is there a way I can run these queries like system procedures?

There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works? In this tip we take a closer look at the built-in commands to allow you to do this.

I recently added a new column to one of my core system tables referenced by a reporting view. When I run the view, the added column is not appearing in my result set! What happened and how can I fix this?

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?

There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works? In this tip we take a closer look at the built-in commands to allow you to do this.

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?

Microsoft provides a good set of basic queries into their system tables. However, some of these procedures lack some additional information that I am interested in and I find myself constantly issuing queries to get this additional data. Is there a way I can run these queries like system procedures?

I recently added a new column to one of my core system tables referenced by a reporting view. When I run the view, the added column is not appearing in my result set! What happened and how can I fix this?

There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works? In this tip we take a closer look at the built-in commands to allow you to do this.

Microsoft provides a good set of basic queries into their system tables. However, some of these procedures lack some additional information that I am interested in and I find myself constantly issuing queries to get this additional data. Is there a way I can run these queries like system procedures?

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?

I recently added a new column to one of my core system tables referenced by a reporting view. When I run the view, the added column is not appearing in my result set! What happened and how can I fix this?