Category Archives: SQL Server

SQL Server: Example Naming Convention

An example of a SQL Server naming convention I have encountered: Tables tcTableName (e.g. tcAsset) for core tables, i.e. tables that contain regularly changing data and have insert/update/delete queries run against them. trTableName (e.g. trAssetType) for reference tables, i.e. tables … Continue reading

Posted in SQL Server | Leave a comment

SQL Server: Disable All Constraints

To disable all constraints on all tables in a database: [sourcecode language=”sql”] EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all” [/sourcecode] To enable all constraints on all tables in a database: [sourcecode language=”sql”] exec sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? … Continue reading

Posted in SQL Server | Leave a comment

SQL Server: Drop all Foreign Keys that Reference a Table

Introduction Before you drop a table in SQL Server, you must first drop all the foreign keys that reference that table. The following SQL scripts may help with this task. Step 1:  Run the following to create a Stored Procedure … Continue reading

Posted in SQL Server | Leave a comment

SQL Server: Show all the FOREIGN KEYS that reference a TABLE

For SQL 2005+ [sourcecode language=”sql”] DECLARE     @tableName nvarchar(MAX) SET     @tableName = ‘tableName’ — (1) SELECT DISTINCT      ccu.table_name,      ccu.constraint_name FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc INNER JOIN      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc           ON rc.unique_constraint_name = tc.constraint_name INNER JOIN      INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu … Continue reading

Posted in SQL Server | Leave a comment

SQL Server: Show all the PRIMARY KEYS for a TABLE

For SQL Server 2005 + [sourcecode language=”SQL”] DECLARE @tableName nvarchar(MAX) SET @tableName = ‘tableName’ — (1) SELECT tc.TABLE_NAME, tc.CONSTRAINT_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND tc.TABLE_NAME = … Continue reading

Posted in SQL Server | Leave a comment

SQL: WHEN CASE NULL Fails

Imagine you want to find all the null values in a column in a database table (SQL Server). x 1 2 NULL 4 5 Here is the SQL that performs the task as required: [sourcecode language=”sql”] SELECT x, CASE x … Continue reading

Posted in SQL Server | 32 Comments

SQL Server: Change Owner of an Object

Like this: [sourcecode language=”sql”] sp_changeobjectowner ‘‘, ‘‘ [/sourcecode]

Posted in SQL Server | Leave a comment

SQL Server: Case Sensitive Query

Someone at work sent this out to the team. From LW: Dear All, you might find this bit of code quite useful.  I was trying to find data within a table where the data was like lower case letters e.g … Continue reading

Posted in SQL Server | Leave a comment