Here are some examples of how to check for existing objects in SQL Server. Use them before you drop/create/modify to avoid errors.
Object Type Existence Checks:
- column
- column data type
- default value
- foreign key
- function
- index
- linked server
- primary key
- schema
- stored procedure
- table
- login (server level)
- user (database level)
- view
- BONUS: current database
Column Check
Using INFORMATION_SCHEMA.COLUMNS
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'MyColumn') BEGIN PRINT 'Altering MyTable to include the MyColumn'; ALTER TABLE dbo.MyTable ADD MyColumn INT NULL; END GO
Using COL_LENGTH
IF COL_LENGTH('MyTable', 'MyColumnName') IS NOT NULL BEGIN PRINT 'Dropping MyColumnName column on MyTable.'; ALTER TABLE MyTable DROP COLUMN MyColumnName; END GO
Column Data Type Check
Check if a column has a datatype of varchar(200), if not, change it
IF NOT EXISTS( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = 200 AND TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'MyColumn') BEGIN PRINT 'Changing datatype of MyTable.MyColumn to vachar(200)'; ALTER TABLE MyTable ALTER COLUMN MyColumn NVARCHAR(200) NULL; END
Column Default Value Check
IF NOT EXISTS( SELECT * FROM sys.all_columns c JOIN sys.tables t ON t.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = t.schema_id JOIN sys.default_constraints d ON c.default_object_id = d.object_id WHERE s.name = 'MySchema' AND t.name = 'MyTable' AND c.name = 'MyColumn') BEGIN ALTER TABLE [MyTable] ADD CONSTRAINT [MyConstraint] DEFAULT ((0)) FOR [MyColumn]; END GO
Foreign Key Check
IF OBJECT_ID('FK_MyForeignKey', 'F') IS NOT NULL BEGIN PRINT 'Removing old foreign key reference'; ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [FK_MyForeignKey]; END GO
Function (Scalar) Check
IF OBJECT_ID ('MyFunction'. 'FN') IS NOT NULL BEGIN PRINT 'Dropping MyFunction'; DROP FUNCTION MyFunction; END GO
Index Check
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'idx_MyIndex') BEGIN PRINT 'Dropping idx_MyIndex to recreate it.' DROP INDEX idx_MyIndex ON dbo.MyTable; END GO PRINT 'Creating idx_MyIndex'; GO CREATE NONCLUSTERED INDEX idx_MyIndex ON [dbo].[MyTable] ([myRelatedCol1],[myRelatedCol2],[myRelatedCol3]) INCLUDE ([myRelatedCol4]) GO
Linked Server Check
IF NOT EXISTS( SELECT * FROM sys.servers WHERE name = N'MyLinkedServerAliasName') BEGIN --do something END
Primary Key Check
--check for PK on a schema'd table IF EXISTS ( SELECT * FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_OBJECT_ID) = 'MyTable' AND SCHEMA_NAME([schema_id]) = 'MySchema' ) BEGIN PRINT 'Dropping primary key from MySchema.MyTable'; ALTER TABLE [MySchema].[MyTable] DROP CONSTRAINT PK_MyPrimaryKey END GO
Schema Check
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MySchema') BEGIN PRINT 'Creating schema MySchema'; EXEC('CREATE SCHEMA [MySchema] AUTHORIZATION [dbo]'); END
Stored Procedure Check
IF OBJECT_ID(N'MyProcedure', 'P') IS NOT NULL BEGIN PRINT 'Dropping MyProcedure'; DROP PROCEDURE MyProcedure; END GO
Table Check
IF OBJECT_ID(N'dbo.MyTable', 'U') IS NOT NULL BEGIN PRINT N'Dropping dbo.MyTable'; DROP TABLE dbo.MyTable; END GO
Login (Server Level) Check
IF NOT EXISTS ( SELECT * FROM master.sys.server_principals WHERE name = 'MyServerLoginName') BEGIN PRINT 'Creating server login: MyServerLoginName'; CREATE LOGIN [MyServerLoginName] WITH PASSWORD = N'Password', DEFAULT_DATABASE = [MyDatabase], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF END GO
User (Database Level) Check
IF NOT EXISTS( SELECT * FROM sys.database_principals WHERE name = 'MyDbUsername') BEGIN --do someting PRINT 'Creating database user'; CREATE USER [MyDbUsername] FOR LOGIN [MyServerLoginName] END
You can also link the database user up with the server login like so:
USE MyDatabase; SELECT sp.name AS ServerLoginName, dp.name AS DBUserName FROM sys.server_principals sp LEFT JOIN sys.database_principals dp ON sp.sid = dp.sid WHERE sp.name = 'MyLogin';
View Check
IF OBJECT_ID(N'MyView', 'V') IS NOT NULL BEGIN PRINT 'Dropping MyView. Its no longer needed.'; DROP VIEW [dbo].[MyView]; END GO
Current Executing Database
IF (db_name() = 'MyDatabase') BEGIN PRINT 'We are executing this call against MyDatabase'; END GO
Leave a Reply