how to find number of columns in a table in sql server 2000
and 2005 also
Answer Posted / anuruddha
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON
C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
Is This Answer Correct ? | 3 Yes | 3 No |
Post New Answer View All Answers
Explain the different types of joins?
what is the system function to get the current user's user id? : Sql server database administration
You want to generate a report that is formatted as a chart. Can you use the report wizard to create such a report?
How do you start single user mode in clustered installations?
What the different types of Replication and why are they used?
When I run the sql server 2000 setup, it just hangs. What do I do?
How much memory that we are using in Logshipping Concept?
What is difference between group by and having?
Does order by actually change the order of the data in the tables or does it just change the output?
What does executeupdate return?
Explain primary key and foreign key constraints?
How to provide default values to stored procedure parameters?
What functions can a view be used to performed?
what are user defined datatypes? : Sql server database administration
can we have a nested transaction? : Sql server database administration