My Technical Notes

Wednesday, 20 July 2011

Getting Column Names From SQL Server

To retrieve information about columns of a table in SQL Server you need to excecute the sp_columns stored procedure (example is using Project table):


sp_columns @table_name='Project'

Running this sp gives you more information than you want. You are only interested in the COLUMN_NAME column in the result. To get only COLUMN_NAME you need to use OPENROWSET as below:


SELECT t.COLUMN_NAME 
FROM OPENROWSET(
 'SQLNCLI', 
 'Server=ITSSQLDEV01;Trusted_Connection=yes;Database=WorkPlanning', 
 'exec sp_columns @table_name=''Project''') AS t;

The only inelegant aspect of this solution is that we are passing as an argument, the same connection string which we used to connect to the database server in the first place, to OPENROWSET.

Another method, which does not require OPENROWSET is as follows:


SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Resource'
ORDER BY ORDINAL_POSITION

No comments: