Tips
Auto generate SQL Server UPDATE triggers for data auditing
You have a new requirement that all data changes to your tables need to be audited, so the old value is stored whenever a change is made. One way of doing this is to create audit tables for each of the tables that you need to audit and write to these audit tables when data in the main table has changed. These triggers can be created manually one by one, but in this tip I show you how you can automate the creation of the triggers for each of the tables you need to audit by using a script to generate scripts.
Capture all statements for a SQL Server session
One thing that frustrates me is that sometimes I write these great queries, but often forget to save them or can't remember exactly what the query looked like that I ran five iterations ago. One smart thing to do would be to always save your scripts, but when your in the process of trying a bunch of different things there is always the chance you may loose something. In this tip we look at creating a server side trace for one session and capturing all completed batches that are run, so you can find that great query that you thought you may have lost.
Cross tab queries with SQL Server 2000
In SQL Server 2000 there is no simple way to create a cross tab query. This may come as a big surprise to people that have been doing this in Microsoft Access for quite some time. With Microsoft Access there is a built in option to produce crosstab queries, but not so with SQL Server 2000. This tip shows various implementations for creating crosstab queries using SQL Server 2000.
Executing a TSQL batch multiple times using GO
Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing. Whatever the need the easiest way to do this is to setup a while loop and execute your code, but in SQL 2005 there is an even easier way to do this.
File Validation in SQL Server with xp_fileexist stored procedure
In a recent tip (Accessing the Windows File System from SQL Server) options were outlined to capture files from a specific directory into a temporary table or table variable for additional processing. A similar file operations task that is needed in particular T-SQL code is to validate a specific file exists or not. Then logic can be written to either process the file, retry at a specific interval or fail the process. As such, how can this be accomplished and can you provide me with some concrete examples in SQL Server 2000 and 2005?
Find Text in the Text data type with the SQL Server LIKE Operator
One problem that you may be faced with is the need to find text data that is contained in a larger set of text. There are two ways this can be done either using the LIKE operator or by using Full Text indexing. Let's take a look at some of the options of using the LIKE operator and some pros and cons of this approach.
Finding and listing all columns in a SQL Server database with default values
I was troubleshooting an issue last week on a vendor-developed database when they stated we needed to look at each one of the 50 tables in their database to make sure that all fields expecting default values, had default values assigned. Well, you can imaging how daunting a task that would be and I immediately raised the question: "Don't you have a better way to do this other than to open each table in SQL Server Management Studio to review the schema?" Their answer was a polite, but firm "No". At that point I decided to change their mind...
Kill SQL Server Process Ids (spids)
I have noticed some of my processes are failing because spids are already connected to the database. This happens specifically when I need to a restore database. I catch this problem pretty quick when I am working on it during the data and can fix it, but during nightly processing existing spids become problematic. I have also noticed existing spids causing problems for my SQL Server 2000 Database Maintenance Plans. I have found this issue in my logs specifically related to performing integrity checks (DBCC CHECKDB ('YourDatabaseName') REPAIR_FAST) when the database needs to be in single user mode before the integrity check commands run. How can I kill these spids prior to running my processes?
List columns and attributes for every table in a SQL Server database
Just yesterday I had a colleague ask if I could help document all the columns in each table in one of our databases to share with a vendor working on an interface between that system and one the vendor developed for a different aspect of our business. This vendor needed to know the column names, max length, data type and whether a null value was acceptable for each of the columns in the database. He was planning on opening each table individually within Microsoft SQL Server Management Studio (SSMS) and then document each column individually within Microsoft Excel. There had to be a better way.
Making a more reliable and flexible sp_MSforeachdb
While the system procedure sp_MSforeachdb is neither documented nor officially supported, most SQL Server professionals have used it at one time or another. This is typically for ad hoc maintenance tasks, but many people (myself included) have used this type of looping activity in permanent routines. Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. Since this situation is not easily reproducible, and since Microsoft typically has no interest in fixing unsupported objects, this may be happening in your environment right now.
Obtain Exclusive Access to a SQL Server Database
In your earlier tip (Die Sucker - Killing SQL Server Process Ids (spids)) you outlined an option to kill spids (system process ids) to perform database restores or database maintenance when exclusive use of the database is needed. Do any other options exist? What are the advantages and disadvantages to these approaches? When should one approach be used over another?
Recursive queries with SQL Server 2000
In SQL Server 2000 there is no simple way to create recursive queries that have several levels of data (hierarchical data). Generally a recursive query is needed when you have a parent and child data stored in the same table. As mentioned above there is no simple way to produce recursive queries that have several levels of data, but several implementations have been produced and are available.
Run same command on all SQL Server databases without cursors
There are times when I find myself needing to run a SQL command against each database on one of my SQL Server instances. There is a handy undocumented stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database: sp_MSforeachdb.
Scan a SQL Server Database for Objects and Columns Containing a Given Text Value
Last week we had a conversion and the bank number was going to change during the process. I knew there was a lot of special code written for the bank and I did not want to miss anything. I needed to find an easy way to scan all tables in the database to find any object that contained that specific bank number or any columns in the database that I needed to update. In this tip I will show you the approach I took to easily identify what columns a particular value exists in as well as the how many times this value exists.
Script to check that backup files still exist for SQL Server
You have configured your database backup jobs without any problems. The backups themselves seem to be working, but are the files still where you think they are? How do you know they have not been moved, or worse, deleted to free up some space on your server? And when would you find out that the files were no longer there? If you are looking for the file to do a restore, and cannot find it, then it is too late. In this tip, I will show you a simple way to check through the backup history to find the last full backup for each database and also to check that the file still exists where it is supposed to exist.
Script to Find and Drop All Orphaned Users in All SQL Server Databases
In this tip we look at a script that identifies all orphaned users for all databases on a SQL Server instance. The script can both identify and drop the orphaned users.
Scripting SQL Server Database Objects Using DMO
One of the nice things you can do with Enterprise Manager is script out all of your objects to a source file. You can then use these files to load your source control, make a backup of your database objects or use the scripts to load another database. The way this scripting is done is by using DMO (Distributed Management Objects). The scripting can be done with many different languages and it gives you control over doing repetitive tasks or making your own interface into SQL Server.
Searching and finding a string value in all columns in a SQL Server table
Sometimes there is a need to find if a string value exists in any column in your table. Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column". So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column. Is there any way this can be dynamically generated?
Splitting Delimited Strings Using XML in SQL Server
This article will help developers looking for a way to split delimited strings in a single query using XML. We generally use a user defined function to do this, which you have probably found in many places that splits the string based on the delimiter passed. But, when it comes to separating the string in a single query without any help of a user defined function there are not many options. I have found a much simpler and shorter way of splitting any string based on a delimiter. I will be using the power of XML to do the splitting of the string instead of a user defined function.
SQL Server Find and Replace Values in All Tables and All Text Columns
In a previous tip, Searching and finding a string value in all columns in a SQL Server table, you showed how to find a string value in any text column in any table in a database. I was wondering how this can be taken a step further to allow a replacement of text in a string data type for all columns and tables in my database. I have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.
SQL Server Row Count for all Tables in a Database
I am a database tester and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target database. How do I get the row counts from all the tables in a SQL Server Database? What are the different approaches to get this information? Check out this tip to get these questions and more answered.
SQL Server Script to Create Windows Directories
Most SQL Server Database Administrators have specific standards for storing their database files on the disk drives. In our environment many of our instances host multiple databases, and though database names must be unique on a Microsoft SQL Server instance, I do not like to drop all of the database and/or log files into the default data and log directories on my SQL Server instances. To make file management easier, I create a subdirectory for each database in the default data and log paths. I was curious if I could create the database file folders dynamically, without needing to open an Windows Explorer session in order to create the new database folder each time I created a database...
Standardize SQL Server data with text lookup and replace function
Have you ever had the need to replace multiple words in a data column within SQL Server with a new word or a new phrase for an entire table? A good example would be to update a product catalog with new words either for standardization or just to reflect a new name for the product. A simple technique would be to use the REPLACE command as outlined in this prior tip. This is great if you only need to update a few words, but what if you need to scan the entire product catalog and make several changes across the board, sometimes multiple changes for one data value?
Top 10
Script to Find and Drop All Orphaned Users in All SQL Server Databases
In this tip we look at a script that identifies all orphaned users for all databases on a SQL Server instance. The script can both identify and drop the orphaned users.
SQL Server Row Count for all Tables in a Database
I am a database tester and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target database. How do I get the row counts from all the tables in a SQL Server Database? What are the different approaches to get this information? Check out this tip to get these questions and more answered.
Kill SQL Server Process Ids (spids)
I have noticed some of my processes are failing because spids are already connected to the database. This happens specifically when I need to a restore database. I catch this problem pretty quick when I am working on it during the data and can fix it, but during nightly processing existing spids become problematic. I have also noticed existing spids causing problems for my SQL Server 2000 Database Maintenance Plans. I have found this issue in my logs specifically related to performing integrity checks (DBCC CHECKDB ('YourDatabaseName') REPAIR_FAST) when the database needs to be in single user mode before the integrity check commands run. How can I kill these spids prior to running my processes?
SQL Server Find and Replace Values in All Tables and All Text Columns
In a previous tip, Searching and finding a string value in all columns in a SQL Server table, you showed how to find a string value in any text column in any table in a database. I was wondering how this can be taken a step further to allow a replacement of text in a string data type for all columns and tables in my database. I have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.
Making a more reliable and flexible sp_MSforeachdb
While the system procedure sp_MSforeachdb is neither documented nor officially supported, most SQL Server professionals have used it at one time or another. This is typically for ad hoc maintenance tasks, but many people (myself included) have used this type of looping activity in permanent routines. Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. Since this situation is not easily reproducible, and since Microsoft typically has no interest in fixing unsupported objects, this may be happening in your environment right now.
Run same command on all SQL Server databases without cursors
There are times when I find myself needing to run a SQL command against each database on one of my SQL Server instances. There is a handy undocumented stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database: sp_MSforeachdb.
Cross tab queries with SQL Server 2000
In SQL Server 2000 there is no simple way to create a cross tab query. This may come as a big surprise to people that have been doing this in Microsoft Access for quite some time. With Microsoft Access there is a built in option to produce crosstab queries, but not so with SQL Server 2000. This tip shows various implementations for creating crosstab queries using SQL Server 2000.
Searching and finding a string value in all columns in a SQL Server table
Sometimes there is a need to find if a string value exists in any column in your table. Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column". So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column. Is there any way this can be dynamically generated?
Finding and listing all columns in a SQL Server database with default values
I was troubleshooting an issue last week on a vendor-developed database when they stated we needed to look at each one of the 50 tables in their database to make sure that all fields expecting default values, had default values assigned. Well, you can imaging how daunting a task that would be and I immediately raised the question: "Don't you have a better way to do this other than to open each table in SQL Server Management Studio to review the schema?" Their answer was a polite, but firm "No". At that point I decided to change their mind...
Recursive queries with SQL Server 2000
In SQL Server 2000 there is no simple way to create recursive queries that have several levels of data (hierarchical data). Generally a recursive query is needed when you have a parent and child data stored in the same table. As mentioned above there is no simple way to produce recursive queries that have several levels of data, but several implementations have been produced and are available.
Last 10
Script to Find and Drop All Orphaned Users in All SQL Server Databases
In this tip we look at a script that identifies all orphaned users for all databases on a SQL Server instance. The script can both identify and drop the orphaned users.
SQL Server Row Count for all Tables in a Database
I am a database tester and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target database. How do I get the row counts from all the tables in a SQL Server Database? What are the different approaches to get this information? Check out this tip to get these questions and more answered.
Making a more reliable and flexible sp_MSforeachdb
While the system procedure sp_MSforeachdb is neither documented nor officially supported, most SQL Server professionals have used it at one time or another. This is typically for ad hoc maintenance tasks, but many people (myself included) have used this type of looping activity in permanent routines. Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. Since this situation is not easily reproducible, and since Microsoft typically has no interest in fixing unsupported objects, this may be happening in your environment right now.
Script to check that backup files still exist for SQL Server
You have configured your database backup jobs without any problems. The backups themselves seem to be working, but are the files still where you think they are? How do you know they have not been moved, or worse, deleted to free up some space on your server? And when would you find out that the files were no longer there? If you are looking for the file to do a restore, and cannot find it, then it is too late. In this tip, I will show you a simple way to check through the backup history to find the last full backup for each database and also to check that the file still exists where it is supposed to exist.
List columns and attributes for every table in a SQL Server database
Just yesterday I had a colleague ask if I could help document all the columns in each table in one of our databases to share with a vendor working on an interface between that system and one the vendor developed for a different aspect of our business. This vendor needed to know the column names, max length, data type and whether a null value was acceptable for each of the columns in the database. He was planning on opening each table individually within Microsoft SQL Server Management Studio (SSMS) and then document each column individually within Microsoft Excel. There had to be a better way.
Splitting Delimited Strings Using XML in SQL Server
This article will help developers looking for a way to split delimited strings in a single query using XML. We generally use a user defined function to do this, which you have probably found in many places that splits the string based on the delimiter passed. But, when it comes to separating the string in a single query without any help of a user defined function there are not many options. I have found a much simpler and shorter way of splitting any string based on a delimiter. I will be using the power of XML to do the splitting of the string instead of a user defined function.
Auto generate SQL Server UPDATE triggers for data auditing
You have a new requirement that all data changes to your tables need to be audited, so the old value is stored whenever a change is made. One way of doing this is to create audit tables for each of the tables that you need to audit and write to these audit tables when data in the main table has changed. These triggers can be created manually one by one, but in this tip I show you how you can automate the creation of the triggers for each of the tables you need to audit by using a script to generate scripts.
Capture all statements for a SQL Server session
One thing that frustrates me is that sometimes I write these great queries, but often forget to save them or can't remember exactly what the query looked like that I ran five iterations ago. One smart thing to do would be to always save your scripts, but when your in the process of trying a bunch of different things there is always the chance you may loose something. In this tip we look at creating a server side trace for one session and capturing all completed batches that are run, so you can find that great query that you thought you may have lost.
SQL Server Find and Replace Values in All Tables and All Text Columns
In a previous tip, Searching and finding a string value in all columns in a SQL Server table, you showed how to find a string value in any text column in any table in a database. I was wondering how this can be taken a step further to allow a replacement of text in a string data type for all columns and tables in my database. I have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.
Scan a SQL Server Database for Objects and Columns Containing a Given Text Value
Last week we had a conversion and the bank number was going to change during the process. I knew there was a lot of special code written for the bank and I did not want to miss anything. I needed to find an easy way to scan all tables in the database to find any object that contained that specific bank number or any columns in the database that I needed to update. In this tip I will show you the approach I took to easily identify what columns a particular value exists in as well as the how many times this value exists.
