Difference between revisions of "SQL Server"
From Richard's Wiki
(→Run a command on each table in a database) |
|||
| Line 5: | Line 5: | ||
Use the sp_msforeachtable undocumented system stored proc, for example: | Use the sp_msforeachtable undocumented system stored proc, for example: | ||
| − | <nowiki>sp_msforeachTable @command1='select count(*) NR from ? where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar''</nowiki> | + | <nowiki> |
| + | sp_msforeachTable @command1='select count(*) NR from ? | ||
| + | where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar'' | ||
| + | ' | ||
| + | </nowiki> | ||
| + | |||
| + | To restrict tables to those in the dbo schema, use the @whereAnd parameter: | ||
| + | |||
| + | <nowiki> | ||
| + | sp_msforeachTable @command1='select count(*) NR from ? | ||
| + | where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar'' | ||
| + | ' , | ||
| + | @whereAnd = 'and uid = 1' | ||
| + | </nowiki> | ||
Revision as of 02:33, 5 January 2010
Run a command on each table in a database
Use the sp_msforeachtable undocumented system stored proc, for example:
sp_msforeachTable @command1='select count(*) NR from ? where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar'' '
To restrict tables to those in the dbo schema, use the @whereAnd parameter:
sp_msforeachTable @command1='select count(*) NR from ? where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar'' ' , @whereAnd = 'and uid = 1'
