5/16/2018

Schema query to find all Tables with their columns details in SQL Server


Schema Query to get details like columns, schema and Table name about all Tables with in a Database

First changes your database name here in first line of Code, where you want to fetch views details:

USE [DatabaseName]

 

SELECT DB_name() 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 AS t

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

ORDER BY  [Schema Name], [Table Name];

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]