Monthly Archives: June 2015

How to map the table structure of a SQL Server Database

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
  • ColumnName
  • IsPrimaryKeyYes
  • IsForeignKeyYes

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 Select column query
  • Each column bracketed
  • Each table and column bracketed

This query is especially useful from a reporting perspective for a DBA or SQL developer unfamiliar with the structure of the database they are querying. 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 database server 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.

Image of excel file with mapped database server structure

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.

USE [master]
GO

--DROP TEMP TABLES IF THEY EXIST
IF OBJECT_ID('tempdb..#ServerStructure') IS NOT NULL
	DROP TABLE #ServerStructure
GO

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
GO

IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer
GO

IF OBJECT_ID('tempdb..#PrimaryKeyTable') IS NOT NULL
	DROP TABLE #PrimaryKeyTable
GO

IF OBJECT_ID('tempdb..#ForeignKeyTable') IS NOT NULL
	DROP TABLE #ForeignKeyTable
GO

--CREATE TEMP TABLES
CREATE TABLE [#ServerStructure] (
	[DatabaseName] VARCHAR(100)
	,[ObjectName] [sysname] NOT NULL
	,[object_id] [int] NOT NULL
	) ON [PRIMARY]
GO

CREATE TABLE [#TableStructure] (
	[SchemaName] VARCHAR(200)
	,[TableName] VARCHAR(200)
	,[ColumnName] VARCHAR(400)
	) ON [PRIMARY]
GO

CREATE TABLE [#PrimaryKeyTable] (
	[TableName] VARCHAR(100)
	,[ColumnName] [sysname] NOT NULL
	,[IsPrimaryKeyYes] [int] NOT NULL
	) ON [PRIMARY]
GO

CREATE TABLE [#ForeignKeyTable] (
	[TableName] VARCHAR(100)
	,[ColumnName] [sysname] NOT NULL
	,[IsForeignKeyYes] [int] NOT NULL
	) ON [PRIMARY]
GO

--THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(400)) ON [PRIMARY]
GO

--sp_msforeachdb WILL RUN A SQL QUERY AGAINST EACH DATABASE ON THE SERVER
--IN THIS INSTANCE THE QUERY WILL GET THE DATABASE, OBJECTNAME (I.E. TABLE NAME) AND THE ASSOCIATED OBJECTID FOR THAT OBJECT
sp_msforeachdb 'INSERT INTO [#ServerStructure] SELECT "?" AS DatabaseName, NAME AS ObjectName, OBJECT_ID from [?].sys.tables';

--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
SELECT dbid AS Id
	,NAME AS DatabaseName
INTO #DatabaseList
FROM master.dbo.sysdatabases (NOLOCK)

--VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP
DECLARE @sqlCommand AS VARCHAR(400)
DECLARE @DbName AS VARCHAR(100)
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 [DatabaseName]
			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 SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,T.NAME AS TableName	
	,C.NAME AS ColumnName
FROM SYS.TABLES AS t (NOLOCK)
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
--WHERE C.NAME IS NOT NULL
ORDER BY SchemaName
	,TableName;
'

	--ERROR HANDLING
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	--CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE
	--WHERE THE COLUMN IS THE PRIMARY KEY
	SET @sqlCommand = 'USE ' + @DbName + ';' + '

INSERT INTO #PrimaryKeyTable
SELECT [TABLE_NAME] AS [TableName]
,[COLUMN_NAME] AS [ColumnName]
,1 AS [IsPrimaryKeyYes]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE (NOLOCK)
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), ''IsPrimaryKey'') = 1;
'

	--ERROR HANDLING
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	--CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE
	--WHERE THE COLUMN IS THE FOREIGN KEY
	SET @sqlCommand = 'USE ' + @DbName + ';' + '

INSERT INTO #ForeignKeyTable
SELECT [TABLE_NAME] AS [TableName]
,[COLUMN_NAME] AS [ColumnName]
,1 AS [IsForeignKeyYes]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE (NOLOCK)
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), ''IsForeignKey'') = 1;
'

	--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
	,SS.DatabaseName
	,TS.SchemaName
	,SS.ObjectName AS TableName
	,TS.ColumnName
	,[IsPrimaryKeyYes]
	,[IsForeignKeyYes]
	,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn
	,',' + QUOTENAME(SS.ObjectName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn
	,'SELECT * FROM ' + QUOTENAME(@@SERVERNAME) + '.' + QUOTENAME(SS.DatabaseName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(SS.ObjectName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable]
	,'SELECT ' + QUOTENAME(SS.ObjectName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(@@SERVERNAME) + '.' + QUOTENAME(SS.DatabaseName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(SS.ObjectName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn]
INTO #MappedServer
FROM [#ServerStructure] SS
INNER JOIN [#TableStructure] TS ON SS.ObjectName = TS.TableName
LEFT JOIN [#PrimaryKeyTable] PKT ON TS.TableName = PKT.TableName
	AND TS.ColumnName = PKT.ColumnName
LEFT JOIN [#ForeignKeyTable] FKT ON TS.TableName = FKT.TableName
	AND TS.ColumnName = FKT.ColumnName

--HOUSE KEEPING
IF OBJECT_ID('tempdb..#ServerStructure') IS NOT NULL
	DROP TABLE #ServerStructure
GO

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
GO

--THE DATA RETURN CAN NOW BE EXPORTED TO EXCEL
--USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS
SELECT *
FROM #MappedServer
ORDER BY DatabaseName
Advertisements