1

I am trying to get column names for a Given table. So I wrote a query like this: 

  SELECT   sc.Name
   FROM     Asdim.dbo.sysobjects so
            INNER JOIN Asdim.dbo.syscolumns sc ON so.id = sc.id
            INNER JOIN Asdim.dbo.systypes st ON sc.xtype = st.xusertype
   WHERE    so.Name = 'Admin'

The problem is that I have two tables with name 'Admin' but they have different schemas. So when I run this query: 

SELECT * FROM Asdim.dbo.sysobjects
WHERE name LIKE 'Admin'

I get two records since the table names are same. Is there a way that I caould filter out based on the schema name too?

James Johnson
  • 45,496
  • 8
  • 73
  • 110
peter
  • 2,396
  • 6
  • 24
  • 29

1 Answers1

2

Using the INFORMATION_SCHEMA tables will be easier and more portable:

SELECT c.column_name
  FROM information_schema.columns c
 WHERE c.table_name = 'Admin' and c.table_schema = 'SCHEMA'

More info on INFORMATION_SCHEMA.COLUMNS.

mellamokb
  • 56,094
  • 12
  • 110
  • 136