CODEWITHSUNDEEP
c#.netsqlvisual-studio
Penguen
Penguen

Reputation: 17278

How can i learn Table Name in database an column name?

How can i learn table Name in database an how can i learn any Table's Column name?

SELECT Col.COLUMN_NAME, Col.DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS AS Col

       LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS Usg ON Col.TABLE_NAME = Usg.TABLE_NAME AND Col.COLUMN_NAME = Usg.COLUMN_NAME

       LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS Con ON Usg.CONSTRAINT_NAME = Con.CONSTRAINT_NAME

WHERE Col.TABLE_NAME = 'Addresses_Temp' AND Con.Constraint_TYPE = 'PRIMARY KEY'

But it returns to me empty data:(

Upvotes: 1

Views: 237

Answers (2)

halil gunes
halil gunes

Reputation: 31

Your query structure must be below 

SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='SchemaName' AND TABLE_NAME='TableName'

for example:

SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='Category'

Upvotes: 0

codingbadger
codingbadger

Reputation: 43974

I'm not entirely sure what the question is but here it goes anyway....

Displays Table information

Select * From Information_Schema.Tables

Displays Column information

Select * From Information_Schema.Columns

Displays Table Constraint information

Select * From Information_Schema.Table_Constraints

Here is some further resources relating to the Information Schema views.

http://msdn.microsoft.com/en-us/library/ms186778.aspx

If your query returns no rows then maybe the table doesn't exist anymore. It is named _temp after all

Upvotes: 2

Related Questions