Hi all,

how to find the columns names in a table in sql 2008r2.

as i need to compare the midsing fields in the table from two database

Thanks

Hi,

please refer query as below

SELECT a.name "ColumnName"
FROM syscolumns a,
sysobjects b
WHERE a.id = b.id 
and b.name ='<<tablename>>'
and b.[type] = 'u'

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 1 hour 48 minutes ago

Few more better(ANSI) ways

SELECT a.name "ColumnName" FROM sys.columns a
where a.object_id = object_id('<<tablename>>')

Select Column_name From INFORMATION_SCHEMA.COLUMNS where table_name='<<tablename>>'

Hi, 

please refer the query below:

SELECT ColumnName
FROM
(
    SELECT  c.name "ColumnName"
    FROM  <<db1>>.sys.tables t
    INNER JOIN  <<db1>>.sys.all_columns c 
            ON t.object_id = c.object_id
    INNER JOIN  <<db1>>.sys.types ty 
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = '<<db1_table>>'
    EXCEPT
    SELECT  c.name
    FROM  <<db2>>.sys.tables t
    INNER JOIN  <<db2>>.sys.all_columns c 
            ON t.object_id = c.object_id
    INNER JOIN  <<db2>>.sys.types ty 
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = '<<db2_table>>'
) t1
UNION ALL
SELECT ColumnName
FROM
(
    SELECT  c.name ColumnName
    FROM  <<db2>>.sys.tables t
    INNER JOIN  <<db2>>.sys.all_columns c  
            ON t.object_id = c.object_id
    INNER JOIN  <<db2>>.sys.types ty      
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = '<<db2_table>>'
    EXCEPT
    SELECT  c.name
    FROM  <<db1>>.sys.tables t
    INNER JOIN  <<db1>>.sys.all_columns c 
            ON t.object_id = c.object_id
    INNER JOIN  <<db1>>.sys.types ty 
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = '<<db1_table>>'
) t2;

please repalce as follow

<<db1>> as database name,  

<<db2>> as another database name,

<<db1_table>> as table name in database 

<<db2_table>> as table name in another database.

Thanks