Posted By

g8rpal on 09/30/10


Tagged

list columns


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

bobbym245
Tyster


List all columns in server


 / Published in: SQL
 

  1. --Create temp tables to store sys data
  2. CREATE TABLE #DBList(id int identity (1,1), DBName nvarchar(50))
  3. CREATE TABLE #TableList(id int identity (1,1), DBName nvarchar(50), TableName Nvarchar(200), columnName nvarchar(200))
  4. CREATE TABLE #ColumnList(id int identity (1,1), DBName nvarchar(50), TableName Nvarchar(200), tbi int,columnName nvarchar(200))
  5.  
  6. --get a list of non system DBs
  7. INSERT INTO #DBList(DBName)
  8. SELECT name FROM master..sysDatabases WHERE sid !=0x01
  9.  
  10. --declare variables
  11. declare @i int
  12. declare @max1 int
  13. declare @db nvarchar(50)
  14.  
  15. declare @sql nvarchar(4000)
  16. declare @sql1 nvarchar(4000)
  17. declare @tbi int
  18. declare @colID int
  19. declare @max int
  20. declare @dbname nvarchar(400)
  21. declare @tbid int
  22. declare @tbl nvarchar(50)
  23. declare @svr AS nvarchar(50)
  24.  
  25.  
  26.  
  27.  
  28. --set initial state of variables
  29. SET @i=1                                                                 --counter
  30. SET @max1 =(SELECT max(id) FROM #DBList)+1         --maximum value to iterate to
  31. SET @svr= upper(@@servername)                            --name of server
  32. SET @colID=1                                                        
  33.  
  34.  
  35.  
  36. ---gets a list of databases from server and populate #DBList table
  37.         while @i <@max1
  38.                 begin
  39.                     SET @db = (SELECT DBname FROM #DBList where @i=id)
  40.                     SET @sql ='select '''+@db+''' ,id,name from '+ @db+'..sysobjects
  41.                                                                                 where xtype =''u'''
  42.                     SET @i=@i+1
  43.                     INSERT INTO #ColumnList(DbName,tbi, TableName)
  44.                     exec (@sql)
  45. end
  46.  
  47. --get details based on column id
  48.     SET @max=(SELECT max(id) FROM #ColumnList)+1
  49.         while @colID < @max
  50.             begin
  51.                 SET @dbname=(SELECT dbname FROM #ColumnList where id=+ @colID)
  52.                 SET @tbid=(SELECT tbi FROM #ColumnList where id=+ @colID)
  53.                 SET @tbl=(SELECT tablename FROM #ColumnList where id=+ @colID)
  54.                 SET @sql1='select '''+@dbname +''','''+@tbl+''' ,name from ['+@dbname +']..syscolumns
  55.                 where id='+ cast(@tbid AS nvarchar(20))
  56.                 INSERT INTO #TableList(dbname,Tablename,columnName)
  57.                 exec(@sql1)
  58.                 SET @colID=@colID+1
  59. end
  60.  
  61.  
  62.  
  63. --Select from Temp Table
  64. SELECT @svr AS Svr,DBName,TableName,ColumnName
  65. FROM #TableList
  66. ORDER BY DBname,tablename, columnname
  67.  
  68.  
  69. --CleanUp
  70. DROP TABLE #DBList
  71. DROP TABLE #TableList
  72. DROP TABLE #ColumnList

Report this snippet  

You need to login to post a comment.