Examples for checking if SQL Server Objects exist

CheckObjectExistenceSqlServer.sql

/*
CHECK FOR OBJECTS IN MS SQL SERVER
Examples of how to check for existing objects in SQL Server.  
Use them before you drop/create/modify to avoid errors.
*/


--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

-------------------------------------------------------
--Column Check
--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
	PRINT 'Do something..';
END
go

-------------------------------------------------------
--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
go

-------------------------------------------------------
--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
    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