May 19, 2010 at 7:18 am
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.
May 19, 2010 at 8:19 am
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
May 20, 2010 at 12:59 am
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
May 20, 2010 at 6:11 am
Query is looking good.
Nag
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 20, 2010 at 6:14 am
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
May 20, 2010 at 6:23 am
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
May 20, 2010 at 7:01 am
Thanks ALL. Thanks lot.
June 4, 2010 at 11:11 am
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.
September 9, 2010 at 2:26 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply