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 '''[email protected]_name+''', name,id from '[email protected]_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 '''[email protected]_name+''''+','''[email protected]_name+''', name from '[email protected]_name+'.dbo.syscolumns where id =' [email protected] +' 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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a