How to get schemaname, tablename, identity column, foreign key constraints

Question

How to get schemaname, tablename, identity column, foreign key constraints

nithiyanandam-447806

Ten Centuries

Points: 1197
More actions
May 19, 2010 at 7:18 am

#386966

Hi,
i need to create a select query to get schemaname, tablename, identity column, foreign key constraints for whole database. for using dynamic query to use bulk import i need to get these informations.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 1

Dynamic methods are frequently problematic, but to get the data you're looking for, check out the system views available under the schema called INFORMATION_SCHEMA. They should supply you with everything you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

nithiyanandam-447806 Ten Centuries Points: 1197 More actions · Answer 2

I tried to get the above qry. could you please check and verify this query...

select s.name schemaname, o.name tablename, i.name identitycolname,

ins.constraint_name constraintname

from sys.schemas s

join sys.sysobjects o on o.uid = s.schema_id

left join sys.identity_columns i on o.id = i.object_id

left join information_schema.constraint_table_usage ins on

ins.tabnle_name = o.name

and ins.constraint_name in ( select insc.constraint_name from

information_schema.referential_constraints insc )

where o.xtype = 'U'

order by s.name, o.name

Thanks in advance..

Nithiyanandam.S

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 3

Query is looking good.

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 4

Nagesh S-432384 (5/20/2010)
Query is looking good.
Nag

you just miss spelled the table_name as tabnle_name 🙂 nothing else is the problem.

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 5

It really depends on your needs. That query is returning the data you outlined.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

nithiyanandam-447806 Ten Centuries Points: 1197 More actions · Answer 6

nithiyanandam-447806

Ten Centuries

Points: 1197

May 20, 2010 at 7:01 am

#1170081

Thanks ALL. Thanks lot.

lucasm-630215 Old Hand Points: 302 More actions · Answer 7

Okay how about taking this one step further, and show the column names that are in the Foreign Key? Is there a way? Each time I attempt that I end up with errors.

Lacc SSC-Addicted Points: 471 More actions · Answer 8

Hello!

Something like this:

SELECT

CTU.TABLE_SCHEMA + '.' + CTU.TABLE_NAME

,

KCU.COLUMN_NAME [COLUMN],

CTU2.TABLE_SCHEMA + '.' + CTU2.TABLE_NAME [REFERENCED_TABLE],

KCU2.COLUMN_NAME [REFERENCED_COLUMN],

CTU.CONSTRAINT_NAME [FK_CONSTRAINT]

FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU

JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME AND KCU.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA

JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME AND RC.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA

JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU2 ON CTU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND CTU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA

JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA

WHERE CTU.TABLE_NAME = 'tablename'

AND CTU.CONSTRAINT_NAME LIKE 'FK_%'

Lacc