HawkeSoft SQL Tips and Tricks - Part 1 - Writing SQL with SQL

This is the first in a series of posts describing SQL tips and tricks for SQL Server 2000/2005/2008. We at HawkeSoft hope you enjoy this series, and as always, we welcome your feedback!

Writing SQL with SQL:
Want to know the number of rows in every table in your database? No problem, until you realize your database has hundreds of tables. Try the code sample below for a quick dynamic SQL solution (SQL Server 2005).

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT COUNT(1) AS [' + ST.name + ' Count] FROM ' + ST.name + ' ' FROM sys.tables ST

--SELECT @SQL
EXEC(@SQL)

As you can see this approach is very powerful and can be applied in many different situations such as database maintenance commands, code re-compilations, updating statistics, etc...

If this type of code interests you should also check out the undocumented stored procedures sp_MSforeachtable and sp_MSforeachdb.

Remember, when dealing with code that has the potential to update several objects at once in your database ALWAYS make sure it is going to do what you anticipate, and consider the performance impacts. Happy coding!