Quick and easy way to get list of all the tables with all the columns and columns demographic from SQL Server Databases on SQL Server Instance
Example:
Working on a project
and needed to get list of all the tables from all the databases with columns
and columns demographic. This information can be needed for many reasons.
The below script uses
cursor to loop through all the databases to get the list of tables with columns.
I have ignored the system databases such as master, model, msdb and tempdb. You
can filter the databases on which you would like to run the query.
________________________________________________________
USE Master
GO
--Declare Variables
DECLARE @DatabaseName AS VARCHAR(500)
--Create Temp Table to Save your Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (
ServerName VARCHAR(150)
,DatabaseName VARCHAR(150)
,SchemaName VARCHAR(150)
,TableName VARCHAR(150)
,ColumnName VARCHAR(150)
,Data_Type VARCHAR(150)
,Is_Nullable VARCHAR(25)
,Character_Maximum_Length VARCHAR(10)
)
DECLARE CUR CURSOR
FOR
--Filter the Database for which you would like to get all the tables with columns
SELECT '[' + NAME + ']' AS DBName
FROM sys.databases
WHERE NAME NOT IN (
'master'
,'tempdb'
,'model'
,'msdb'
)
OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--Build dynamic sql for each database
DECLARE @SQL VARCHAR(MAX) = NULL
SET @SQL = 'Insert into #Results
Select
@@ServerName,
Table_Catalog as DatabaseName,
Table_Schema AS TableSchema,
Table_Name AS TableName,
Column_Name AS ColumnName
,Data_Type
,Is_Nullable
,Character_Maximum_Length
From' + @DatabaseName + '.information_Schema.Columns
where Table_Name in (Select Table_Name from ' + @DatabaseName + '.information_schema.Tables
where table_type=''BASE TABLE'')'
EXEC (@SQL)
PRINT @SQL
FETCH NEXT
FROM Cur
INTO @DatabaseName
END
CLOSE Cur
DEALLOCATE Cur
SELECT *
FROM #Results
order by DatabaseName,TableName
_______________________________________________________________
I hope it helps someone out there. Feel free to add comments or ask questions.
Thanks
I am happy to find this post very useful for me, as it contains a lot of information. I always prefer to read the quality content and this thing I found in your post. Thanks for sharing it. 10GBPS Dedicated Server
ReplyDeleteSuch a great post you have shared here about Networks. I would like to thank you for posting such great post.Arista Networks Houston Keep it up.
ReplyDelete