Today I
was asked for "a list of all tables in all databases" on a particular
instance of SQL Server. Knowing what was
actually required was all "User" tables in all "Accessible"
"User" databases I wrote the script below . It will return the
ServerName, DatabaseName, SchemaName and TableName of all the user tables in
all online read-writeable non system databases.
Hopefully someone will find
this useful ;)
/*
-----------------------------------------------------------------
Get all user tables for all online,
read-writable user databases
-----------------------------------------------------------------
For more SQL resources, check out
SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own
purposes.
You may republish altered code as long as
you give due credit.
You must obtain prior permission before
blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED
"AS IS"
-----------------------------------------------------------------
*/
-- Declare
variables
DECLARE @MinDBID INT
DECLARE @MaxDBID INT
DECLARE
@DatabaseName VARCHAR(100)
DECLARE @SQL VARCHAR(2000)
-- Check for
temporary tables and drop them if they exists
IF OBJECT_ID('tempDB.dbo.#Database')
IS NOT NULL
DROP TABLE [#Database] ;
IF OBJECT_ID('tempDB.dbo.#Table') IS NOT NULL
DROP TABLE [#Table] ;
-- Create
temporary tables
CREATE TABLE #Database
(
ID INT IDENTITY(1, 1),
DatabaseName VARCHAR(100)
);
CREATE TABLE #Table
(
ID INT IDENTITY(1, 1),
ServerName VARCHAR(100),
DatabaseName VARCHAR(255),
SchemaName VARCHAR(255),
TableName VARCHAR(255)
);
-- Get online read/writeable user
databases
INSERT INTO
#Database (
DatabaseName )
SELECT name
FROM sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB' )
AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' ;
--Set variables
loop
SELECT @MinDBID = MIN(ID),
@MaxDBID =
MAX(ID)
FROM #Database
-- Begin loop
WHILE @MinDBID <= @MaxDBID
BEGIN
-- Get DatabaseName
SELECT @DatabaseName =
DatabaseName
FROM #Database
WHERE ID =
@MinDBID
-- Set SQL to run for each
database
SET @SQL = ' USE ' +
@DatabaseName + ';'
+ '
INSERT INTO #Table
SELECT
@@ServerName
AS ServerName,
DB_NAME() AS
DatabaseName,
s.name AS
SchemaName,
t.name AS
TableName
FROM
sys.tables
AS t
INNER JOIN
sys.schemas as s on s.[schema_id] = t.[schema_id]
WHERE
[type] =
''U''
AND is_ms_shipped
= 0' + ';'
-- Try catch block to execute
SQL and handle errors
BEGIN TRY
-- Get table information
EXEC ( @SQL
)
END TRY
BEGIN CATCH
SELECT @DatabaseName,
message_id,
severity,
[text],
@SQL
FROM sys.messages
WHERE message_id =
@@ERROR
AND
language_id = 1033 -- British
English
END CATCH
-- Get the next database
SET
@MinDBID = @MinDBID +
1
-- End loop
END
-- Return results
SELECT ID,
ServerName,
DatabaseName,
SchemaName,
TableName
FROM #Table
ORDER BY DatabaseName,
SchemaName, TableName ASC;
GO
Enjoy
Chris
