Recently I had to examine the content of almost all tables in all databases to find necessary tables and recreate the entity model. So I need a script that can iterate thru all tables in all databases on a given server.
This can be made with the help of undocumented stored procedure sp_msforeachdb. The examples of use can be found via Google search.Here is my script that lists all tables on a MS SQL server.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE master; GO DECLARE @Tables TABLE ( DatabaseName sysname, SchemaName sysname, TableName sysname ); INSERT INTO @Tables (DatabaseName, SchemaName, TableName) EXEC sp_msforeachdb 'select ''?'', s.name, t.name from [?].sys.tables t inner join [?].sys.schemas s on t.schema_id = s.schema_id'; SELECT * FROM @Tables ORDER BY 1, 2, 3; |