SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General / How to get schemaname, tablename, identity column, foreign key constraints / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:25:00 GMT20RE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxHello!Something like this:[code="sql"]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_SCHEMAWHERE CTU.TABLE_NAME = 'tablename'AND CTU.CONSTRAINT_NAME LIKE 'FK_%'[/code]LaccThu, 09 Sep 2010 02:26:26 GMTLaccRE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxOkay 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.Fri, 04 Jun 2010 11:11:42 GMTlucasm-630215RE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxThanks ALL. Thanks lot.Thu, 20 May 2010 07:01:20 GMTnithiyanandam-447806RE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxIt really depends on your needs. That query is returning the data you outlined.Thu, 20 May 2010 06:23:44 GMTGrant FritcheyRE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspx[quote][b]Nagesh S-432384 (5/20/2010)[/b][hr]Query is looking good.Nag[/quote]you just miss spelled the table_name as tabnle_name :-) nothing else is the problem.NagThu, 20 May 2010 06:14:13 GMTNagesh S-432384RE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxQuery is looking good.NagThu, 20 May 2010 06:11:38 GMTNagesh S-432384RE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxI 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 constraintnamefrom sys.schemas sjoin sys.sysobjects o on o.uid = s.schema_idleft join sys.identity_columns i on o.id = i.object_idleft 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.nameThanks in advance..Nithiyanandam.SThu, 20 May 2010 00:59:25 GMTnithiyanandam-447806RE: How to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxDynamic 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.Wed, 19 May 2010 08:19:14 GMTGrant FritcheyHow to get schemaname, tablename, identity column, foreign key constraintshttp://www.sqlservercentral.com/Forums/Topic924278-391-1.aspxHi,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.Wed, 19 May 2010 07:18:36 GMTnithiyanandam-447806