5/16/2018

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


Schema Query to get details like columns, schema and views name about all views 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],

v.name as [View Name],

SCHEMA_NAME(schema_id) AS [Schema Name],

c.name AS [Column Name],

'Select Top 10 * From ['+ DB_name() +'].['+SCHEMA_NAME(schema_id)+'].['+v.name+']' as Query_String

from  sys.views v

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

 

Result will be as:

Database Name
View 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]