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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to get schemaname, tablename, identity...

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

Rate Topic

Display Mode

Topic Options

Author

Message

nithiyanandam-447806

Posted Wednesday, May 19, 2010 7:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 01, 2013 8:45 AM
Points: 47, Visits: 253

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.

Post #924278

Grant Fritchey

Posted Wednesday, May 19, 2010 8:19 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #924331

nithiyanandam-447806

Posted Thursday, May 20, 2010 12:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 01, 2013 8:45 AM
Points: 47, Visits: 253

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

Post #924852

Nagesh S-432384

Posted Thursday, May 20, 2010 6:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 03, 2013 12:04 AM
Points: 110, Visits: 458

Query is looking good.

Nag

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

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

Post #925031

Nagesh S-432384

Posted Thursday, May 20, 2010 6:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 03, 2013 12:04 AM
Points: 110, Visits: 458

Nagesh S-432384 (5/20/2010)

Query is looking good.

Nag

you just miss spelled the table_name as tabnle_name Smile

nothing else is the problem.

Nag

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

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

Post #925033

Grant Fritchey

Posted Thursday, May 20, 2010 6:23 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #925044

nithiyanandam-447806

Posted Thursday, May 20, 2010 7:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 01, 2013 8:45 AM
Points: 47, Visits: 253

Thanks ALL. Thanks lot.

Post #925078

lucasm-630215

Posted Friday, June 04, 2010 11:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 06, 2012 3:51 AM
Points: 4, Visits: 39

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.

Post #932968

Lacc

Posted Thursday, September 09, 2010 2:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:11 AM
Points: 7, Visits: 226

Hello!

Something like this:

SELECT 
CTU.TABLE_SCHEMA + '.' + CTU.TABLE_NAME [TABLE],
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

Post #982903

« Prev Topic | Next Topic »

Permissions