This post uses the script I had written before here LINK modified slightly to include a count of each table. If you are a DBA you’re likely assisting compliance offers to find personal identifiable data within the databases at this time. This script will allow you to provide them with a record of every database, table and column on an entire instance. The compliance offer can then sieve through all the columns and highlight any columns that look like they contain personal data for further investigation.
Below is a SQL query that will return the following metadata about each table from each database located on a SQL server database server:
- ServerName
- DatabaseName
- SchemaName
- TableName
- CountOfRows
- ColumnName
- KeyType
The output is provided in a table format with these additional formatted lines of text which can be used as queries or as part of queries.
- A Select table query
- A Count table row columns
- A Select column query
- Each column bracketed
- Each table and column bracketed
The table returned by the query can be exported to excel. Using excels filter option applied to the columns of the table makes finding and selecting specific tables and columns very easy.
This process can be repeated for every SQL Server instance used by the business to generate a single mapped servers master excel file allowing the user to find any table or column available to the organization quickly.
Applying some colour coding like below adds to the ease of use.
How to use:
Simply open SQL Server Management Studio and from object explorer right click on the server name and select new query. This will open a window set to the master database of the server. Copy and paste the SQL below into this SQL Server window and execute. When the query is finished you will have created the table above.
/*
SCRIPT UPDATED
20180316
*/
USE [master]
GO
/*DROP TEMP TABLES IF THEY EXIST*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
DROP TABLE #DatabaseList;
IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
DROP TABLE #TableStructure;
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
DROP TABLE #ErrorTable;
IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
DROP TABLE #MappedServer;
IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
DROP TABLE #TableCount;
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
DROP TABLE #Count;
DECLARE @ServerName AS SYSNAME
DECLARE @Count INT
SET @ServerName = @@SERVERNAME
CREATE TABLE #DatabaseList (
Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,ServerName SYSNAME
,DbName SYSNAME
);
CREATE TABLE [#TableStructure] (
[DbName] SYSNAME
,[SchemaName] SYSNAME
,[TableName] SYSNAME
,[ColumnName] SYSNAME
,[KeyType] CHAR(7)
) ON [PRIMARY];
CREATE TABLE [#TableCount] (
[Id_TableCount] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,[ServerName] SYSNAME
,[DatabaseName] SYSNAME
,[SchemaName] SYSNAME
,[TableName] SYSNAME
,[SQLCommand] VARCHAR(MAX)
,[TableCount] INT
);
CREATE TABLE #Count (ReturnedCount INT);
/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];
/*
A LIST OF DISTINCT DATABASE NAMES IS CREATED
THESE TWO COLUMNS ARE STORED IN THE #DatabaseList TEMP TABLE
THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME
*/
INSERT INTO #DatabaseList (
ServerName
,DbName
)
SELECT @ServerName
,NAME AS DbName
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC
/*VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP*/
DECLARE @sqlCommand AS VARCHAR(MAX)
DECLARE @DbName AS SYSNAME
DECLARE @i AS INT
DECLARE @z AS INT
SET @i = 1
SET @z = (
SELECT COUNT(*) + 1
FROM #DatabaseList
)
/*WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN #DatabaseList*/
WHILE @i < @z
BEGIN
/*GET NEW DATABASE NAME*/
SET @DbName = (
SELECT [DbName]
FROM #DatabaseList
WHERE Id = @i
)
/*CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE*/
SET @sqlCommand = 'USE [' + @DbName + '];' + '
INSERT INTO [#TableStructure]
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
,T.NAME AS TableName
,C.NAME AS ColumnName
,CASE
WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
THEN ''Primary''
WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsForeignKey'') = 1
THEN ''Foreign''
ELSE NULL
END AS ''KeyType''
FROM SYS.TABLES AS t WITH (NOLOCK)
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS iskcu WITH (NOLOCK)
ON SCHEMA_NAME(SCHEMA_ID) = iskcu.TABLE_SCHEMA
AND T.NAME = iskcu.TABLE_NAME
AND C.NAME = iskcu.COLUMN_NAME
ORDER BY SchemaName ASC
,TableName ASC
,ColumnName ASC;
';
/*ERROR HANDLING*/
BEGIN TRY
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
INSERT INTO #ErrorTable
SELECT (@sqlCommand)
END CATCH
SET @i = @i + 1
END
/*
JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER
ADDITIONAL FIELDS ARE ADDED TO MAKE SELECTING TABLES AND FIELDS EASIER
*/
SELECT DISTINCT @@SERVERNAME AS ServerName
,DL.DbName
,TS.SchemaName
,TS.TableName
,TS.ColumnName
,TS.[KeyType]
,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn
,',' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn
,'SELECT * FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable]
,'SELECT ' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn]
,'SELECT COUNT(*) FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + 'WITH (NOLOCK)' AS [PerformTableCount]
INTO #MappedServer
FROM [#DatabaseList] AS DL
INNER JOIN [#TableStructure] AS TS ON DL.DbName = TS.DbName
ORDER BY DL.DbName ASC
,TS.SchemaName ASC
,TS.TableName ASC
,TS.ColumnName ASC
/*
HOUSE KEEPING
*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
DROP TABLE #DatabaseList;
IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
DROP TABLE #TableStructure;
INSERT INTO #TableCount (
[ServerName]
,[DatabaseName]
,[SchemaName]
,[TableName]
,[SQLCommand]
)
SELECT DISTINCT [ServerName]
,[DbName]
,[SchemaName]
,[TableName]
,[PerformTableCount]
FROM #MappedServer
ORDER BY [ServerName] ASC
,[DbName] ASC
,[SchemaName] ASC
,[TableName] ASC
SET @i = 1
SET @z = (
SELECT COUNT(*) + 1
FROM #TableCount
)
WHILE @i < @z
BEGIN
SET @SQLCommand = (
SELECT SQLCommand
FROM #TableCount
WHERE Id_TableCount = @i
)
--ERROR HANDLING
BEGIN TRY
INSERT INTO #Count
EXEC (@SqlCommand)
END TRY
BEGIN CATCH
INSERT INTO #ErrorTable
SELECT (@sqlCommand)
END CATCH
SET @Count = (
SELECT ReturnedCount
FROM #Count
)
TRUNCATE TABLE #Count
UPDATE #TableCount
SET TableCount = @Count
WHERE Id_TableCount = @i;
SET @i = @i + 1
END
SELECT *
FROM #ErrorTable;
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
DROP TABLE #ErrorTable;
/*
THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL
USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS
*/
SELECT DISTINCT ms.ServerName
,ms.DbName
,ms.SchemaName
,ms.TableName
,ts.TableCount AS CountOfRows
,ms.ColumnName
,ms.KeyType
,ms.BracketedColumn
,ms.BracketedTableAndColumn
,ms.SelectColumn
,ms.SelectTable
FROM #MappedServer AS ms
LEFT JOIN #TableCount AS ts ON ms.ServerName = ts.ServerName
AND ms.DbName = ts.DatabaseName
AND ms.SchemaName = ts.SchemaName
AND ms.TableName = ts.TableName
ORDER BY ms.DbName ASC
,ms.SchemaName ASC
,ms.TableName ASC
,ms.ColumnName ASC;
IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
DROP TABLE #MappedServer;
IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
DROP TABLE #TableCount;
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
DROP TABLE #Count;
Like this:
Like Loading...
Tidbytez 