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

Question

I am trying to list all the columns from the Adventureworks table that I select. What T-sQL statement or stored procedure can I execute to list all columns? I want to use my C # web application to input a single input parameter = table_name, and then get a list of all column columns as output. Right now I'm trying to execute the sp_columns executable, which works, but I can’t get only one column with the select and exec command. Does anyone know how to do this?

Thank you all for all your answers, but none of your answers do what I need. Let me explain my problem more to you. The following query returns what I need. But my problem is to include this logic in the SP, which takes an input parameter. So here is a 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 is as follows:

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 this gives me an error:

Invalid column name 'AddressType'

How to do it?

Answer 1

You do not need to create a statement as a string - in fact, that you are mistaken. Try the following:

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

Answer 2

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

Answer 3

database.dbo.yourtable :

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

EDIT OP

sql, :

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

1, @TableName. , .

Answer 4

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

Answer 5

Remus Rusanu SqlConnection.GetSchema() #

Answer 6

:

script , , , where. :

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 

? 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, this is not a problem - either obj.name = (regardless of what they passed) or not.