Run a command against each table
While I generally use NimbleText to run a command against each table, there are some handy features built into SQL Server that let you do this in a quite seamless manner.
sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
exec sp_msforeachtable @Command1 = 'print ''reindexing table ?''', @Command2 = 'dbcc dbreindex(''?'')', @Command3 = 'select count (*), '?' as TableName [?] from ?'
Here's a minimum example to try for yourself:
exec sp_msforeachtable 'select count (*), '?' as TableName [?] from ?'
You can change the character to use for tablename by supplying a second parameter exec sp_MSforeachtable 'print "Should I drop ?"', ''
Honestly, I wouldn't use this feature at all.
I much prefer the versatility of getting table lists from Information_Schema or even SysObjects, and then turning those results into queries with NimbleText.
- NimbleText: How to run the same query against many tables
- Stack Overflow: Hidden Features of SQL Server
sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)