General discussion

Locked

RE: Alter every table in a Database

By petera232 ·
There's a quicker way to accomplish executing a command against every table in a database. The Master database has an undocumented stored procedure: sp_msforeachtable. Although it is undocumented by Microsoft, a number of web sites have re-created the documentation, since the text of the stored procedure is readable in the database. One such site is http://www.bstsoftware.com/tsug/Nov99/sp_MSforeachtable.html

In short, you could accomplish your alter every table task by executing:

exec sp_msforeachtable 'ALTER TABLE ? ADD LastUpdated TimeStamp NULL'

A similar example is to return the number of rows in each table. This example is useful because it shows how to include the name of the table in the viewable results:

sp_MSforeachtable 'select count(*) "?" from ?'

The output of this in Query Analyzer is the same as a batch of statements executed directly: each statement creates a grid, or if your output is text mode, each statement has its own header column (not so useful when each statement returns one row with a header). In order to consolidate the output you can make use of the 'precommand' and 'postcommand' parameters:

--------------------------

declare @cmd nvarchar(4000)
declare @pre nvarchar(4000)
declare @post nvarchar(4000)

select @pre = 'create table ##temp1 (table_name char(50), row_count int)'
select @cmd = 'insert into ##temp1 (table_name, row_count) select cast(''?'' as char(50)), count(*) from ? '
select @post = 'select * from ##temp1 where row_count > 0 order by table_name drop table ##temp1'

exec sp_msforeachtable @command1=@cmd, @precommand = @pre, @postcommand = @post

--------------------------

This displays a single grid with all the row counts for tables that have rows.

Also, there is a companion stored procedure sp_MSforeachdatabase, which can streamline administrative tasks that need to be executed against each database in a SQLServer instance.

Cheers, Peter

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums