Retrieve All Columns from Each Table in Every Database

Retrieve All Columns from Each Table in Every Database

This stored procedure retrieves all the columns of each table in every database.

create procedure SP_GetAllColumnsFromAllDB/*---------------------------------------------------------------------- Procedure to retrieve columns of all the databases-- Usage   : exec SP_GetAllColumnsFromAllDB-- Logic is: First get all the DB into a master cursor     excluding system databases-- (1 Master, 2 tempdb, 3 model, 4 msdb, 5 pubs, 6 northwind) -- if you want to include this then just remove the   where condition from master cursor i.e.  where dbid≫6-- loop through master cursor -- insert all table name into a temporary table #tmp_table-- Create a inner cursor for tables-- loop through this cursor-- insert all the columns (into #final_table) of looping table(in Master looping database)-- delete the table names from #tmp_table to be able to get the tables of next database 
-------------------------------------------------------------------- */asbegin declare @db_name varchar(1000) declare @table_name varchar(1000) declare @col_name varchar(1000) declare @id varchar(1000) declare @sql nvarchar(4000) create table #tmp_table (db varchar(100),name varchar(2000), id int) create table #final_table (db varchar(100),tname varchar(2000),cname varchar(2000)) -- Declare cursor for getting all the databases except -- system databases declare cur_db cursor for select name from master.dbo.sysdatabases where dbid>6 --excluding (1 Master,2 tempdb,3 model,4 msdb,5 pubs, --6 northwind) open cur_db fetch next from cur_db into @db_name while (@@fetch_status<>-1) begin -- Insert name of all the tables into #tmp_table for this -- database and use it set @sql='insert into #tmp_table (db,name,id) Select '''+@db_name+''', name,id from '+@db_name+'.dbo.'+'sysobjects where xtype in (''u'')' execute sp_executesql @sql --columns declare cur_column cursor for select * from #tmp_table open cur_column fetch next from cur_column into @db_name,@table_name,@id while (@@fetch_status<>-1) begin -- Insert column name into #final_table for this -- table (in which database this table belongs) set @sql='insert into #final_table (db,tname,cname) Select '''+@db_name+''''+','''+@table_name+''', name from '+@db_name+'.dbo.syscolumns where id =' +@id +' order by colorder' execute sp_executesql @sql fetch next from cur_column into @db_name,@table_name,@id end close cur_column deallocate cur_column set @sql='' -- Delete all rows from #tmp_table delete from #tmp_table fetch next from cur_db into @db_name end select db as 'database name',tname as 'table name',cname as 'Column' from #final_table order by db,tname close cur_db deallocate cur_db drop table #tmp_table drop table #final_tableend
See also  Essential Measures for Safeguarding Your Digital Data

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist