SQL List all Columns and Data Types in Database and Server

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:

Here is a sample of the query results:

SQL List all Columns and Data Types in Database and Server

 

I hope you find the example above informative.  Please use and modify to suit your needs.

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

 

Leave a Comment