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
ANDlanguage_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
