Use sp_msforeachdb to search information in whole MS SQL Server
If you use dedicated server to one project you need usually search anything in whole SQL server. There are very useful procedure for this perpose - sp_msforeachdb. This is example of using this procedures.
1: ALTER procedure [dbo].[ListAllTables]
2: as
3: SET NOCOUNT ON
4: DECLARE @AllTables table (TABLE_CATALOG nvarchar(128),TABLE_SCHEMA nvarchar(128),TABLE_NAME nvarchar(128),TABLE_TYPE varchar(10))
5: INSERT INTO @AllTables
6: EXEC sp_msforeachdb 'select * from [?].INFORMATION_SCHEMA.TABLES where TABLE_TYPE=''BASE TABLE'' or TABLE_TYPE=''VIEW'''
7: SET NOCOUNT OFF
8: SELECT * FROM @AllTables where
9: not (TABLE_CATALOG ='master' or TABLE_CATALOG ='tempdb' or TABLE_CATALOG ='msdb')
10: and charindex('#', TABLE_NAME) <> 1
11: order by TABLE_CATALOG, TABLE_TYPE , TABLE_NAME, TABLE_SCHEMA
And this is result, in this case project Voypusk use 634 tables and views.
Comments (
)
)
Link to this page:
http://www.vb-net.com/sp_msforeachdb/Index.htm
|
|