Exploit the power of T-SQL in generating scripts for repetitive tasks
Last updated: December 1st '01 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window
About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:    
  Database programming guidelines, conventions SQL Server DBA and developer interview questions
  Evaluation of federated database servers SQL Server FAQ on programming, designing, administration, tools, replication
  SQL Server security best practices SQL Server administration best practices

In Microsoft SQL Server newsgroups, I very often see questions like:

"I need to change the owner of some 500 tables from admin to dbo. Is there a quick way of doing this?"

"I need to grant EXECUTE permissions on all my stored procedures to a particular user. I know I can do this using GRANT statement, but it's a pain to repeat the command for each stored procedure. Is there a way out?"

So, I decided to come up with this page to show you, how this kind of tasks can be automated, by letting SQL Server do all the hard work (It's not really hard for SQL Server ;-) and generate the scripts for you. Here I will discuss some scenarios, but you should be able to alter this technique to suit your own needs.

Idea is to use SELECT statements to output the required commands. Then you have to copy the output into the query window , and run it.

I'm discussing the following 5 scenarios here:


Scenario 1 <top>
Changing the owner of all the tables in your database to dbo:

SELECT 'EXEC sp_changeobjectowner ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', dbo'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE' /*Check the TABLE_TYPE against 'VIEW' to work with views*/
--AND TABLE_SCHEMA = 'Admin1' /*To change the owner of tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To change the owner of tables starting with a particular pattern*/
GO

The above script outputs the required command. Copy them, make sure the output is exactly what you wanted, and run the commands. Make sure you login as dbo before running this script, for better control.

As you can see, I commented the last two conditions in the WHERE clause using double hyphens (--). Uncomment them, change the values as needed, if you have to customize the output.


Scenario 2 <top>
Granting EXECUTE permissions on all the stored procedures to a particular user, named WebUser:

SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO ' + '[WebUser]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name)), 'IsMSShipped') = 0
--AND name LIKE 'Rep%' /*To grant EXECUTE permission on only procedures starting with Rep*/
GO

The above script outputs the required commands. Copy them, make sure the output is exactly what you wanted, and run the commands.

As you can see, I commented the last condition in the WHERE clause using double hyphens (--). Uncomment it, change the value as needed, if you have to limit the output to a particular name pattern of stored procedures.


Scenario 3 <top>
Truncating data from all the tables in a database:

SELECT 'TRUNCATE TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To truncate tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To truncate tables starting with a particular pattern*/
GO

The above script outputs the required commands. Copy them, make sure the output is exactly what you wanted, and run the commands. Make sure you login as dbo before running this script, for better control.

As you can see, I commented the last two conditions in the WHERE clause using double hyphens (--). Uncomment them, change the value as needed, if you have to customize the output. Note that you will not be able to truncate a table that is referenced by a foreign key constraint, in which case you have to use the DELETE command instead.


Scenario 4 <top>
Temporarily disable the constraints on all the tables, and re-enable them later:

/*To disable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To disable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To disable constraints on tables starting with a particular pattern*/
GO

/*To enable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To enable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To enable constraints on tables starting with a particular pattern*/
GO

The above two scripts output the required commands. Copy them, make sure the output is exactly what you wanted, and run the commands. Make sure you login as dbo before running this script, for better control.

As you can see, I commented the last two conditions in the WHERE clause using double hyphens (--). Uncomment them, change the value as needed, if you have to customize the output.


Scenario 5 <top>
Temporarily disable all the triggers on all the tables, and re-enable them later:

/*To disable triggers*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' DISABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To disable triggers on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To disable triggers on tables starting with a particular pattern*/
GO

/*To enable triggers*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' ENABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To enable triggers on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To enable triggers on tables starting with a particular pattern*/
GO

The above two scripts output the required commands. Copy them, make sure the output is exactly what you wanted, and run the commands. Make sure you login as dbo before running this script, for better control.

As you can see, I commented the last two conditions in the WHERE clause using double hyphens (--). Uncomment them, change the value as needed, if you have to customize the output.

Write SET NOCOUNT ON at the beginning of all these scripts to avoid the (n row(s) affected) message in the output.

Comments? Feedback? Click here to send a mail!

Disclaimer and terms of use
Copyright © 1997 - 2004 Narayana Vyas Kondreddi. All rights reserved.