Just dived into Microsoft.SqlServer.Management.Smo.Database object (which is provided by Microsoft itself!) They simply do this using following statement: CAST(case when dtb.name in (‘master’,’model’,’msdb’,’tempdb’) then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject] In short: if a database is named master, model, msdb or tempdb, it IS a system db; it is also a system db, … Read more
You can’t disable triggers directly and I wouldn’t recommend doing what you’re suggesting but you could have your trigger check if a variable (in my example below @disable_triggers) is NULL before executing the trigger’s content. For example: Query: SET @disable_triggers = 1; // Your update statement goes here. SET @disable_triggers = NULL; Triggers: IF @disable_triggers … Read more
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
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
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
To get all tables with columns columnA or ColumnB in the database YourDatabase: SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘columnA’,’ColumnB’) AND TABLE_SCHEMA=’YourDatabase’;
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