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]