Microsoft® SQL Server 2008 R2 Unleashed

Generating T-SQL Statements with T-SQL

The system catalogs in SQL Server 2008 contain a wealth of information you can use to save a lot of time and effort when generating SQL statements that need to be run repeatedly on a large number of database objects or when trying to build a column list for a query. You can use T-SQL code to select information from the system catalogs, system tables, and system views to generate SELECT statements and the like. For example, say you want to grant EXECUTE permission to the user fred on each stored procedure in the bigpubs2008 database. This can be a tedious task to perform using SSMS because in the Securables dialog for the user fred, you have to select each procedure, one at a time, and click the Grant Execute ...