SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK

how to find number of columns in a table in sql server 2000
and 2005 also

Question Posted / anuruddha

7 Answers
14306 Views
HCL, Virtusa, I also Faced
E-Mail Answers

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

Please Help Members By Posting Answers For Below Questions

what is a transaction? : Sql server database administration

983

How to write a query with a full outer join in ms sql server?

1063

What happens if you add a new index to large table?

929

How to create a stored procedure with a statement block in ms sql server?

1060

How can we get count of the number of records in a table?

943

Write a code to select distinct records without using the DISTINCT keyword.

1107

What is default port number for sql server 2000?

1031

What do I need to start working with sql studio? : sql server management studio

1103

How do I repair damaged sql server mdf file database? In previous day my mdf file has got damage due to unknown reasons then I used dbcc chekcdb command but it failed, MDF file is important for me, I don’ know that how to get back mdf file data. Please anyone suggest me?

1744

A user is a member of the public role and the sales role. The public role has select permission on all the tables. The sales role does not have select permission on some of the tables will the user be able to select from all tables?

953

What is the report builder?

139

What is difference between materialized view and view?

903

Delete duplicate rows without using rowid.

1530

According to you what goes into making the best database administrator? : sql server database administration

971

How to change the system date and time from SQL Plus Terminal ?

1210