CODEWITHSUNDEEP
sql-servert-sqlexecadventureworks
salvationishere
salvationishere

Reputation: 3511

In T-SQL how to display columns for given table name?

I am trying to list all of the columns from whichever Adventureworks table I choose. What T-sQL statement or stored proc can I execute to see this list of all columns? I want to use my C# web app to input one input parameter = table_name and then get a list of all the column_names as output. Right now I am trying to execute the sp_columns stored proc which works, but I can't get just the one column with select and exec combined. Does anybody know how to do this?

Thanks everyone for all your replies, but none of your answers do what I need. Let me explain my problem more for you. The query below returns what I need. But my problem is incorporating this logic into an SP that takes an input parameter. So here is the successful SQL statement:

select col.name from sysobjects obj inner join syscolumns col 
    on obj.id = col.id
    where obj.name = 'AddressType' 
     order by obj.name

And currently my SP looks like:

CREATE PROCEDURE [dbo].[getColumnNames]
    @TableName VarChar(50)
    AS

    BEGIN
    SET NOCOUNT ON;
    SET @TableName = RTRIM(@TableName)
    DECLARE @cmd AS NVARCHAR(max)
    SET @cmd = N'SELECT col.name from sysobjects obj ' +
    'inner join syscolumns col on obj.id = col.id ' +
    'where obj.name = ' + @TableName
    EXEC sp_executesql @cmd
    END

But I run the above as

exec getColumnNames 'AddressType'

and it gives me error:

Invalid column name 'AddressType'

How do I accomplish this?

Upvotes: 3

Views: 9401

Answers (6)

KM.
KM.

Reputation: 103637

to display info for database.dbo.yourtable try this:

SELECT
    *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
        TABLE_CATALOG   ='database'
        AND TABLE_SCHEMA='dbo'
        AND TABLE_NAME  ='yourtable'
    ORDER BY ORDINAL_POSITION

EDIT based on OP's edit

You don't need dynamic sql, just use the given parameter:

CREATE PROCEDURE [dbo].[getColumnNames]
@TableName VarChar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Rows         int
           ,@ReturnValue  int
    SET @ReturnValue=0
    SELECT
        col.name 
        FROM sysobjects            obj 
            inner join syscolumns  col on obj.id = col.id 
        WHERE obj.name = @TableName
        ORDER BY obj.name
    SELECT @Rows=@@ROWCOUNT
    IF @Rows=0
    BEGIN
        SET @ReturnValue= 1  --table not found!!
    END
    RETURN @ReturnValue 

END

if you check the return value and get a 1 then no table was found matching the given @TableName parameter. You can use this to give a error message in the application.

Upvotes: 3

egrunin
egrunin

Reputation: 25073

You don't need to create the statement as a string -- in fact, that's what's messing you up. Try this:

CREATE PROCEDURE [dbo].[getColumnNames] @TableName VarChar(50) AS

BEGIN
SET NOCOUNT ON;
SELECT col.name FROM sysobjects obj 
INNER JOIN syscolumns col ON obj.id = col.id 
WHERE obj.name = @TableName
END

Upvotes: 5

Philip Kelley
Philip Kelley

Reputation: 40359

A reply to your edited question:

Your script does not work because you're building a dynamic string, and within that dynamic string you must add quotes to the string you are placing in your where clause. The revised code would be:

BEGIN 
SET NOCOUNT ON; 
SET @TableName = RTRIM(@TableName) 
DECLARE @cmd AS NVARCHAR(max) 
SET @cmd = N'SELECT col.name from sysobjects obj ' + 
'inner join syscolumns col on obj.id = col.id ' + 
'where obj.name = ''' + @TableName + ''''
EXEC sp_executesql @cmd 
END

However, why are you buliding a dynamic string? This would do the same thing and not require the overhead of dynamic SQL:

BEGIN 
SET NOCOUNT ON; 
SET @TableName = RTRIM(@TableName) 
SELECT col.name from sysobjects obj
inner join syscolumns col on obj.id = col.id
where obj.name = @TableName 
END

If you are worried about SQL injection, is not a problem here -- either obj.name = (whatever they passed in), or it doesn't.

Upvotes: 1

Leon
Leon

Reputation: 3401

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName' AND TABLE_CATALOG = 'DatabaseName'

Upvotes: 2

Gregoire
Gregoire

Reputation: 24872

Use Remus Rusanu answer or use SqlConnection.GetSchema() function from your c# code

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294387

select * from sys.columns where object_id = object_id(@tablename);

Upvotes: 8

Related Questions