Good afternoon everyone, today were going to talk about meta-data.  Specifically data relating to tables in SQL Server.  Have you ever wanted to grab all of the column names of a table for some operation?  Well, here is a break down of a technique that can be used to do it.

The first table that we'll be working with is the "sysobjects" table.  This table contains all objects within a database.  Objects are things such as views, tables, and stored procedures.  If you want to see a full collection of this you can query this table and see it first hand:

view plain print about
1SELECT name
2FROM mydb.dbo.sysobjects
3ORDER BY name

To filter only tables you can add an xtype of "U"

view plain print about
1SELECT *
2FROM mydb.dbo.sysobjects
3WHERE xtype = 'U'
4ORDER BY name

 

Now on to column names, we can get the column names for a specific table by joining this on the "syscolumns" table. 

view plain print about
1SELECT mydb.dbo.syscolumns.name AS column_name
2FROM mydb.dbo.syscolumns
3    INNER JOIN mydb.dbo.sysobjects ON mydb.dbo.syscolumns.id = mydb.dbo.sysobjects.id
4WHERE (mydb.dbo.sysobjects.name = N'table_name')

 

There you have it, working with these metadata tables is incredible useful, good luck on using it!

This entry was posted on June 10, 2009 at 10:55 AM and has received 588 views. There are currently 1 comments. Print this entry.