List all tables for a single database/All databse along with the column names and other details

Niladri.biswas
Posted by Niladri.biswas under Sql Server category on | Points: 40 | Views : 1874
I we need to find the list all tables for a single database along with the column names and other details, we can use the below query

Select 
c.Table_Schema
,c.Table_Catalog
,c.Table_Name
,c.Column_Name
,c.Ordinal_Position
,c.Data_Type
,c.Character_Maximum_Length
,c.Is_Nullable
From information_schema.columns c
ORDER BY c.Table_Name


If we need to extend the above query for all the tables present in all the database, then we can use the below query

DECLARE @t TABLE 
(
SchemaName SYSNAME
,DBName SYSNAME
,TableName SYSNAME
,ColumnName VARCHAR(100)
,OrdinalPosition INT
,DataType VARCHAR(100)
,ColumnLength VARCHAR(100)
,IsNullable VARCHAR(3)
)
INSERT INTO @t (SchemaName,DBName,TableName,ColumnName,OrdinalPosition,DataType,ColumnLength,IsNullable)
EXEC sp_msforeachdb 'SELECT
c.Table_Schema
,c.Table_Catalog
,c.Table_Name
,c.Column_Name
,c.Ordinal_Position
,c.Data_Type
,c.Character_Maximum_Length
,c.Is_Nullable
FROM [?].INFORMATION_SCHEMA.COLUMNS c'
SELECT * FROM @t
ORDER BY DBName,TableName

Comments or Responses

Posted by: Self-innovator on: 12/23/2011 Level:Bronze | Status: [Member] | Points: 10
Great i appreciate your effort..kepp it up

Login to post response