Last week, I had several questions regarding fields and how many tables and views a given field name appeared in our server. I’ve typically used the INFORMATION_SCHEMA built-in views to query this type of information, but this was always at the database level, not the server level.
After a bit of research, I came across the following undocumented stored procedure that suited my needs. This stored procedure is called sp_msforeachdb and is very useful when trying to pull information about each database on a given server. In the example below, I will show you how to build a list of all the columns that appear in each table or view on a server, excluding the system tables (master, msdb, model and tempdb).
SQL List all Columns and Data Types in Database and Server T-SQL Code:
IF OBJECT_ID('tempdb..#TableList') IS NOT NULL
DROP TABLE #TableList
CREATE TABLE #TableList (
, TableName nvarchar(128)
, ColumnName nvarchar(128)
, DataType nvarchar(128)
, ObjectType varchar(10));
EXEC master.sys.sp_msforeachdb 'use[?];
INSERT INTO #TableList
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_CATALOG <> ''master''
AND c.TABLE_CATALOG <> ''model''
AND c.TABLE_CATALOG <> ''msdb''
AND c.TABLE_CATALOG <> ''tempdb'';'
SELECT * FROM #TableList
I hope you find the example above informative. Please use and modify to suit your needs.