Category Archives: SQL Server

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 →

To disable all constraints on all tables in a database: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" To enable all constraints on all tables in a database: exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

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 →

For SQL 2005+ 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           ON … Continue reading →

For SQL Server 2005 + 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 = @tableName OR … Continue reading →

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: SELECT x, CASE x WHEN NULL … Continue reading →

Like this: sp_changeobjectowner '<objectname>', '<newownername>'

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 →