Getting the names of columns in a table

Posted At : June 10, 2009 10:55 AM | Posted By : Jonny Stopchick
Related Categories: SQL

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:

SELECT name
FROM mydb.dbo.sysobjects
ORDER BY name

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

SELECT *
FROM mydb.dbo.sysobjects
WHERE xtype = 'U'
ORDER BY name

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

SELECT mydb.dbo.syscolumns.name AS column_name
FROM mydb.dbo.syscolumns
INNER JOIN mydb.dbo.sysobjects ON mydb.dbo.syscolumns.id = mydb.dbo.sysobjects.id
WHERE (mydb.dbo.sysobjects.name = N'table_name')

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

Comments (1) |

Print |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 375 Views

Comments

[Add Comment] [Subscribe to Comments]

Lets not forget the <cfdbinfo> tag and all of its powers in Coldfusion 8

http://www.cfquickdocs.com/cf8/#cfdbinfo

But thats only if your using Coldfusion.

# Posted By AnyMan | 6/10/09 1:00 PM

[Add Comment]