Home
[SQL] - Aggregate SQL Server
May 25, 2018 |
1. Drop INDEX
* SQL Server
-- Check a index is existing or not before drop it.
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'<table_name') AND name = N'<index_name>'))
DROP INDEX <index_name> ON <table_name>
2. Drop TABLE
* SQL Server
-- check a table is existing or not before drop it.
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'table_name'))
DROP TABLE <table_name>
3. Drop COLUMN
* SQL Server
-- drop a column if its doesn't has any contraint.
IF EXISTS (Select 1 From sys.columns Where name = '<column_name>' And object_id = OBJECT_ID('<table_name>'))
BEGIN
ALTER TABLE <table_name> DROP COLUMN <column_name>;
END
-- drop a column if its has contraints.
IF EXISTS (Select 1 From sys.columns Where name = '<column_name>' And object_id = OBJECT_ID('<table_name>'))
BEGIN
DECLARE @def varchar(256);
SELECT @def = name FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID('<table_name>') AND COL_NAME(parent_object_id, parent_column_id) = '<column_name>';
if @def is not null
EXEC('ALTER TABLE <table_name> DROP CONSTRAINT ' + @def);
ALTER TABLE <table_name> DROP COLUMN <column_name>;
END
4. Drop CONSTRAINT
* SQL Server
--drop a contraint
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>
-- Check a column is contraint on a table. If its exist, drop and create contraint.
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' AND K.COLUMN_NAME = '<column_name>'
and K.TABLE_NAME = '<table_name>')
BEGIN
declare @pkey varchar(50);
SELECT top 1 @pkey=c.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' and K.TABLE_NAME = '<table_name>'
if(@pkey is not null)
exec('ALTER TABLE <table_name> DROP CONSTRAINT ' + @pkey);
else
set @pkey='pk_<table_name>';
exec('ALTER TABLE <table_name> ADD CONSTRAINT ' + @pkey + ' PRIMARY KEY CLUSTERED (column_name_1, column_name_2,.., column_name_n) WITH (FILLFACTOR = 80)');
END
-- rename column_1 to column_2 and make them a part of primary key
IF EXISTS (SELECT 1 FROM sys.columns WHERE name = 'column_1' AND object_id = OBJECT_ID('table_1'))
BEGIN
DECLARE @pk_name varchar(300);
SELECT @pk_name = name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = (object_id('table_1'));
if @pk_name is not null
exec('ALTER TABLE table_1 DROP CONSTRAINT ' + @pk_name);
else
SET @pk_name='pk_table_1';
EXEC sp_rename 'table_1.column_1', 'column_2', 'COLUMN';
exec('ALTER TABLE table_1 ADD CONSTRAINT ' + @pk_name + ' PRIMARY KEY CLUSTERED (column_1, column_2,..., column_n) WITH (FILLFACTOR = 80)');
END
Updating...
Previous Article
