Script the Default Constraints all tables

SELECT ‘ALTER TABLE ‘+ SCHEMA_NAME(schema_id) + ‘.’ + OBJECT_NAME(parent_object_id) +
‘ DROP CONSTRAINT ‘ + dc.name
FROM sys.default_constraints dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id

GO

SELECT
‘ALTER TABLE ‘+ SCHEMA_NAME(schema_id) + ‘.’ + OBJECT_NAME(parent_object_id) +
‘ ADD CONSTRAINT ‘ + dc.name + ‘ DEFAULT(‘ + definition
+ ‘) FOR ‘ + c.name
FROM sys.default_constraints dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id

Generate the Column script for a Database

SELECT ‘ IF COL_LENGTH (‘+”” + OBJECT_NAME(c.OBJECT_ID) +”’,’+””+c.name+””+’ ) IS NULL BEGIN ALTER TABLE ‘ + OBJECT_NAME(c.OBJECT_ID) + ‘ ADD ‘ + c.name + ‘ ‘+
CASE WHEN t.name NOT IN (‘int’,’image’,’datetime’,’ntext’,’text’)
THEN
CASE WHEN t.name IN (‘numeric’,’decimal’)
THEN t.name+'(‘+CONVERT(VARCHAR,C.PRECISION)+’,’+convert(VARCHAR,CASE WHEN C.max_length=-1 THEN ‘MAX’ ELSE C.scale END)+’)’
WHEN t.name IN (‘char’)
THEN t.name+'(‘+convert(varchar,C.max_length)+’)’
WHEN t.name IN (‘bit’,’bigint’,’INT’,’date’,’DATETIME’,’DATETIME2′,’smallint’,’tinyint’)
THEN t.name
WHEN t.name IN (‘nvarchar’,’nchar’)
THEN t.name+'(‘+convert(varchar,CASE WHEN C.max_length= ‘-1’ THEN ‘MAX’ ELSE CONVERT(VARCHAR,C.max_length/2) END )+’)’
WHEN t.name IN (‘varchar’)
THEN t.name+'(‘+convert(varchar,C.max_length)+’)’
ELSE t.name+'(‘+convert(VARCHAR,CASE WHEN C.max_length=’-1′ THEN ‘MAX’ ELSE convert(varchar,C.scale) END) +’)’ END ELSE t.name END +’ ‘ +
CASE WHEN C.is_nullable = 0 THEN ‘ NOT NULL’ ELSE ‘ NULL’ END +’ END’ [Script]
FROM sys.columns AS c
inner join sys.tables B on c.object_id=B.OBJECT_ID
JOIN sys.types AS t ON c.user_type_id=t.user_type_id

 

GO

SELECT * FROM
(
SELECT ‘ALTER TABLE ‘ + OBJECT_NAME(c.OBJECT_ID) + ‘ ALTER COLUMN ‘ + c.name  + ‘ ‘+ CASE WHEN t.name NOT IN (‘int’,’image’,’datetime’,’ntext’,’text’)
THEN
CASE WHEN t.name IN (‘numeric’)
THEN t.name+'(‘+CONVERT(VARCHAR,C.PRECISION)+’,’+convert(VARCHAR,CASE WHEN C.max_length=-1 THEN ‘MAX’ ELSE C.max_length END)+’)’
ELSE t.name+'(‘+convert(VARCHAR,CASE WHEN C.max_length=’-1′ THEN ‘MAX’ ELSE convert(varchar,C.max_length) END) +’)’ END  ELSE t.name END  + ‘ COLLATE SQL_Latin1_General_CP1_CI_AS’ [Script]
FROM sys.columns AS c
inner join sys.tables B on c.object_id=B.OBJECT_ID
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
)VW
WHERE [Script] LIKE ‘%VARCHAR%’

Output Clause Archive

Output clause is always my all-time favourite feature in SQL. A simple method of archiving the table is shown below.

CREATE TABLE #Employees
(
ID INT IDENTITY(1,1),
EmpNo VARCHAR(100),
EmployeeName VARCHAR(200)
)

CREATE TABLE #EmployeesArchive
(
ID INT IDENTITY(1,1),
EmpNo VARCHAR(100),
EmployeeName VARCHAR(200)
)

INSERT INTO #Employees (EmpNo,EmployeeName)
SELECT '1001','Madav' UNION ALL
SELECT '1002','Rajiv' UNION ALL
SELECT '1003','Dev' UNION ALL
SELECT '1004','Steve'

SET IDENTITY_INSERT #EmployeesArchive ON
DELETE FROM #Employees
OUTPUT
DELETED.ID,DELETED.EmpNo,DELETED.EmployeeName
INTO #EmployeesArchive (id,EmpNo,EmployeeName)
WHERE ID>2
SET IDENTITY_INSERT #EmployeesArchive OFF

SELECT * FROM #Employees
SELECT * FROM #EmployeesArchive

DROP TABLE #Employees
DROP TABLE #EmployeesArchive


Continue reading “Output Clause Archive”