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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 421 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