Here's a tutorial providing a T-SQL script that will return the following fields for each table from each database located in a SQL Server Instance:
https://tidbytez.com/2015/06/01/map-the-table-structure-of-a-sql-server-database/
/*
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;
DECLARE @ServerName AS SYSNAME
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];
/*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]
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;
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 ServerName
,DbName
,SchemaName
,TableName
,ColumnName
,KeyType
,BracketedColumn
,BracketedTableAndColumn
,SelectColumn
,SelectTable
FROM #MappedServer
ORDER BY DbName ASC
,SchemaName ASC
,TableName ASC
,ColumnName ASC;