Microsoft® SQL Server 2012 Unleashed

Generating T-SQL Statements with T-SQL

The system catalogs in SQL Server 2012 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. Suppose, for example, that you want to grant EXECUTE permission to the user fred on each stored procedure in the AdventureWorks2012 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 ...