Snippets
Created by
Dirk Watkins
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 | /*
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
|
Comments (1)
You can clone a snippet to your computer for local editing. Learn more.
Examples of how to check for existing objects in SQL Server (e.g. stored procedures, views, tables, columns, indexes, etc) Use them before you drop/create/modify to avoid errors.
http://dirq.net/2014/05/sql-server-object-existence/