5/16/2018

Schema query to find all tables with column names in all database in SQL Server


Schema Query to find  all Tables from all database with column name and schema.

Below Query will provide all tables with their Database, Schema and columns from all databases in your SQL Server

sp_msforeachdb 'select "?" AS [Database Name],

t.name AS [Table Name],

SCHEMA_NAME(schema_id) AS [Schema Name],

c.name AS [Column Name],

''[''+ DB_name() +''].[''+SCHEMA_NAME(schema_id)+''].[''+t.name+'']'' as Query_String

From [?].sys.tables t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

Where "?" not in (''master'', ''model'',''msdb'',''tempdb'')'

Result will be as:

Database Name
Table Name
Schema Name
Column Name
Query for Top 10 Records
ABC
Employee
HR
EMPID
Select Top 10 * From [ABC].[HR].[EMPID]
ABC
Employee
HR
FirstName
Select Top 10 * From  [ABC].[HR].[FirstName]