[dba-SQLServer] List of fields in a table

CREATE PROCEDURE GetColumnName(@DB_Name varchar(100), @TableName varchar(100))
     DECLARE @Sql as nvarchar(2000)
     SET @Sql = 'SELECT column_name FROM ' + @DB_Name + '.' + 
'INFORMATION_SCHEMA.COLUMNS WHERE table_name = ' + char(39) + 
@TableName + CHAR(39)
     EXEC @Sql
END

Not tested, but should work.


At 12:00 PM 12/14/2009, you wrote:
>Message: 1
>Date: Mon, 14 Dec 2009 08:34:59 -0500
>From: jwcolby <jwcolby at colbyconsulting.com>
>Subject: [dba-SQLServer] List of fields in a table
>To: Dba-Sqlserver <dba-sqlserver at databaseadvisors.com>
>Message-ID: <4B263F03.2000204 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>I need to get a list of fields in a table in any database I want, in 
>a stored procedure.  I have
>found this:
>
>select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME 
>= 'tblOrderData'
>
>but that only appears to work if you execute that from a query in 
>the target database, whereas my
>stored procedure needs to take the database and table name as 
>parameters and go get the list.
>
>How can I get a list of fields in any table in any database, from a 
>stored procedure saved in a
>"library" database?
>
>--
>John W. Colby