Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get schemaname, tablename, identity column, foreign key constraints Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 7:18 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:26 AM
Points: 47, Visits: 285
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
Posted Wednesday, May 19, 2010 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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 Query Performance Tuning
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
Posted Thursday, May 20, 2010 12:59 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:26 AM
Points: 47, Visits: 285
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
Posted Thursday, May 20, 2010 6:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:05 PM
Points: 111, Visits: 493
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
Posted Thursday, May 20, 2010 6:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:05 PM
Points: 111, Visits: 493
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
Post #925033
Posted Thursday, May 20, 2010 6:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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 Query Performance Tuning
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
Posted Thursday, May 20, 2010 7:01 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:26 AM
Points: 47, Visits: 285
Thanks ALL. Thanks lot.
Post #925078
Posted Friday, June 4, 2010 11:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 6, 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
Posted Thursday, September 9, 2010 2:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:07 AM
Points: 7, Visits: 232
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 »

Add to briefcase

Permissions Expand / Collapse