information-schema

SQL Server: Howto get foreign key reference from information_schema?

by

Never mind, this is the correct answer: http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx SELECT KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA ,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME ,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA ,KCU1.TABLE_NAME AS FK_TABLE_NAME ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION ,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON … Read more

What is the most portable way to check whether a trigger exists in SQL Server?

by

There’s also the preferred “sys.triggers” catalog view: select * from sys.triggers where name=”MyTrigger” or call the sp_Helptrigger stored proc: exec sp_helptrigger ‘MyTableName’ But other than that, I guess that’s about it 🙂 Marc Update (for Jakub Januszkiewicz): If you need to include the schema information, you could also do something like this: SELECT (list of … Read more

SQLite Schema Information Metadata

by

You’ve basically named the solution in your question. To get a list of tables (and views), query sqlite_master as in SELECT name, sql FROM sqlite_master WHERE type=”table” ORDER BY name; (see the SQLite FAQ) To get information about the columns in a specific table, use PRAGMA table_info(table-name); as explained in the SQLite PRAGMA documentation. I … Read more

How do I find a default constraint using INFORMATION_SCHEMA?

by

As I understand it, default value constraints aren’t part of the ISO standard, so they don’t appear in INFORMATION_SCHEMA. INFORMATION_SCHEMA seems like the best choice for this kind of task because it is cross-platform, but if the information isn’t available one should use the object catalog views (sys.*) instead of system table views, which are … Read more

How to check if a table exists in a given schema

by

It depends on what you want to test exactly. Information schema? To find “whether the table exists” (no matter who’s asking), querying the information schema (information_schema.tables) is incorrect, strictly speaking, because (per documentation): Only those tables and views are shown that the current user has access to (by way of being the owner or having … Read more

How to check if a Constraint exists in Sql server?

by

try this: SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =’FK_ChannelPlayerSkins_Channels’ — EDIT — When I originally answered this question, I was thinking “Foreign Key” because the original question asked about finding “FK_ChannelPlayerSkins_Channels”. Since then many people have commented on finding other “constraints” here are some other queries for that: –Returns one row for each CHECK, UNIQUE, … Read more