Get all Tables and Columns for each Database

,

This script will get the name of each database from master..sysdatabases, then use each database's Information Schemata to get the tables and columns in each of the databases. Several recent requests for assistance have used this to answer their needs. I ran it across 245 databases and pulled back 1.4 million results in about 30 seconds. It doesn't need anything passed in,  cleans up after itself when done, and be easily be changed into a Stored Procedure if needed on a repetitive basis. Enjoy.

Create table #DB_Name (dbname varchar (100))
Create table #results (dbname varchar (100), tabName varchar (100), colName varchar (100))

Insert into #DB_Name(dbname) 
select [name] from master.dbo.sysdatabases where dbid > 6

DECLARE @dbName varchar (100),  @sql varchar (1000)
set @dbName = ''
DECLARE db_Update  CURSOR
FOR
 SELECT dbname  from #DB_name
  OPEN db_Update
 FETCH NEXT FROM db_Update INTO @dbName

  WHILE (@@fetch_status <> -1)
   BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
set @sql = 'use ['+@dbname+ '] '

set @sql = @sql +' Insert into #results(dbName, TabName, colName) SELECT '
set @sql = @sql + ''''+@dbname+''''+ ' as dbName,table_name, column_name
FROM Information_Schema.Columns'
Exec (@sql) 
  END
 FETCH NEXT FROM db_Update INTO @dbName
END
CLOSE db_Update
DEALLOCATE db_Update

Select * from #results
drop table #db_name
drop table #results

Rate

5 (1)

Share

Share

Rate

5 (1)