Reverse-engineering column dependencies

Whenever we want to ALTER the data type or data length of a column in a table, often we may need to take care of handling underlying dependencies like constraints, indexes by dropping them before ALTER and re-create them after ALTER.

Scripting out primary keys
USE master
go

IF OBJECT_ID(‘sp_script_pk’) IS NOT NULL
BEGIN
DROP PROCEDURE sp_script_pk
END
GO
CREATE PROCEDURE sp_script_pk
(@sch sysname, @tbl sysname,@col sysname)
AS
BEGIN

SELECT
si.name AS pk_name,
‘PK’,
N’ALTER TABLE ‘ + QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’ ADD CONSTRAINT ‘ + QUOTENAME(si.name) + N’ PRIMARY KEY ‘ +
CASE WHEN si.index_id > 1 THEN N’NON’ ELSE N” END + N’CLUSTERED ‘
+
/* key def */ N'(‘ + key_definition + N’)’ +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N’ INCLUDE (‘ + include_definition + N’)’
ELSE N”
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N’ WHERE ‘ + filter_definition ELSE N”
END +
/* with clause – compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N’ WITH (‘ +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = ROW ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + row_compression_partition_list + N’)’ END
ELSE N” END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N’, ‘ ELSE N” END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = PAGE ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + page_compression_partition_list + N’)’ END
ELSE N” END
+ N’)’
ELSE N”
END +
/* ON where? filegroup? partition scheme? */
‘ ON ‘ + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N”)
ELSE psc.name + N’ (‘ + partitioning_column.column_name + N’)’
END
+ N’;’
AS pk_create_statement,
‘ALTER TABLE ‘+ QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’ DROP CONSTRAINT ‘ + QUOTENAME(si.name) + N’;’
AS pk_drop_statement,
is_disabled,
CASE WHEN si.index_id = 1 THEN 1
ELSE 0
END is_clustered
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N’ DESC’ ELSE N” END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
AND sc.name=@sch AND t.name=@tbl
AND is_primary_key=1
AND CHARINDEX(‘[‘+@col+’]’, key_definition)>0
END

GO

EXEC sys.sp_MS_marksystemobject sp_script_pk
GO

Scripting out unique keys
USE master
go

IF OBJECT_ID(‘sp_script_uk’) IS NOT NULL
BEGIN
DROP PROCEDURE sp_script_uk
END
GO

CREATE PROCEDURE sp_script_uk
(@sch sysname, @tbl sysname,@col sysname)
AS
BEGIN

SELECT
si.name AS uk_name,
‘UQ’,
N’ALTER TABLE ‘ + QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’ ADD CONSTRAINT ‘ + QUOTENAME(si.name) + N’ UNIQUE ‘ +
CASE WHEN si.index_id > 1 THEN N’NON’ ELSE N” END + N’CLUSTERED ‘
+
/* key def */ N'(‘ + key_definition + N’)’ +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N’ INCLUDE (‘ + include_definition + N’)’
ELSE N”
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N’ WHERE ‘ + filter_definition ELSE N”
END +
/* with clause – compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N’ WITH (‘ +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = ROW ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + row_compression_partition_list + N’)’ END
ELSE N” END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N’, ‘ ELSE N” END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = PAGE ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + page_compression_partition_list + N’)’ END
ELSE N” END
+ N’)’
ELSE N”
END +
/* ON where? filegroup? partition scheme? */
‘ ON ‘ + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N”)
ELSE psc.name + N’ (‘ + partitioning_column.column_name + N’)’
END
+ N’;’
AS uk_create_statement,
‘ALTER TABLE ‘+ QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’ DROP CONSTRAINT ‘ + QUOTENAME(si.name) + N’;’
AS uk_drop_statement,
is_disabled,
CASE WHEN si.index_id = 1 THEN 1
ELSE 0
END is_clustered

FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N’ DESC’ ELSE N” END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
AND sc.name=@sch AND t.name=@tbl
AND is_unique_constraint=1
AND CHARINDEX(‘[‘+@col+’]’, key_definition)>0

END

GO

EXEC sys.sp_MS_marksystemobject sp_script_uk
GO

Scripting out indexes
USE master
go

IF OBJECT_ID(‘sp_script_index’) IS NOT NULL
BEGIN
DROP PROCEDURE sp_script_index
END
GO

CREATE PROCEDURE sp_script_index
(@sch sysname, @tbl sysname,@col sysname)
AS
BEGIN

SELECT
si.name AS index_name,
‘IX’,
N’CREATE ‘ +
CASE WHEN si.is_unique = 1 then N’UNIQUE ‘ ELSE N” END +
CASE WHEN si.index_id > 1 THEN N’NON’ ELSE N” END + N’CLUSTERED ‘ +
N’INDEX ‘ + QUOTENAME(si.name) + N’ ON ‘ + QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’ ‘
+
/* key def */ N'(‘ + key_definition + N’)’ +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N’ INCLUDE (‘ + include_definition + N’)’
ELSE N”
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N’ WHERE ‘ + filter_definition ELSE N”
END +
/* with clause – compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N’ WITH (‘ +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = ROW ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + row_compression_partition_list + N’)’ END
ELSE N” END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N’, ‘ ELSE N” END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N’DATA_COMPRESSION = PAGE ‘ + CASE WHEN psc.name IS NULL THEN N” ELSE + N’ ON PARTITIONS (‘ + page_compression_partition_list + N’)’ END
ELSE N” END
+ N’)’
ELSE N”
END +
/* ON where? filegroup? partition scheme? */
‘ ON ‘ + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N”)
ELSE psc.name + N’ (‘ + partitioning_column.column_name + N’)’
END
+ N’;’
AS index_create_statement,
‘DROP INDEX ‘+ QUOTENAME(si.name) + N’ ON ‘+QUOTENAME(sc.name) + N’.’ + QUOTENAME(t.name) + N’;’
AS index_drop_statement,
is_disabled,
CASE WHEN si.index_id = 1 THEN 1
ELSE 0
END is_clustered

FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N’ DESC’ ELSE N” END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N’, ‘ + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’),1,2,”)) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
AND sc.name=@sch AND t.name=@tbl
AND is_primary_key=0
AND is_unique_constraint=0
AND CHARINDEX(‘[‘+@col+’]’, key_definition)>0

ORDER BY si.index_id

END

GO

EXEC sys.sp_MS_marksystemobject sp_script_index
GO

Scripting out check constraints

USE master
go

IF OBJECT_ID(‘sp_script_ck’) IS NOT NULL
BEGIN
DROP PROCEDURE sp_script_ck
END
GO

CREATE PROCEDURE sp_script_ck
(@sch sysname, @tbl sysname,@col sysname)
AS
BEGIN

SELECT
name AS constraint_name,’CK’,
N’ALTER TABLE ‘ + QUOTENAME(@sch) + N’.’ +
QUOTENAME(@tbl) + N’ ADD CONSTRAINT ‘ + QUOTENAME(name) + N’ CHECK ‘ +definition
AS constraint_create_statement,
‘ALTER TABLE ‘+ QUOTENAME(@sch) + N’.’ + QUOTENAME(@tbl) + N’ DROP CONSTRAINT ‘ + QUOTENAME(name) + N’;’
AS constraint_drop_statement,
is_disabled,
0
from sys.check_constraints
WHERE SCHEMA_NAME(schema_id)=@sch and object_name(parent_object_id)=@tbl
and CHARINDEX(‘[‘+@col+’]’, definition)>0

END

GO

EXEC sys.sp_MS_marksystemobject sp_script_ck
GO

Scripting out default constraints
USE master
go

IF OBJECT_ID(‘sp_script_df’) IS NOT NULL
BEGIN
DROP PROCEDURE sp_script_df
END
GO

CREATE PROCEDURE sp_script_df
(@sch sysname, @tbl sysname,@col sysname)
AS
BEGIN

SELECT
name AS constraint_name,’DF’,
N’ALTER TABLE ‘ + QUOTENAME(@sch) + N’.’ +
QUOTENAME(@tbl) + N’ ADD CONSTRAINT ‘ + QUOTENAME(name) + ‘ DEFAULT ‘ + definition + ‘ FOR ‘
+QUOTENAME(@col)
AS constraint_create_statement,
‘ALTER TABLE ‘+ QUOTENAME(@sch) + N’.’ + QUOTENAME(@tbl) + N’ DROP CONSTRAINT ‘ + QUOTENAME(name) + N’;’
AS constraint_drop_statement,
1,
0
from sys.default_constraints
WHERE SCHEMA_NAME(schema_id)=@sch and object_name(parent_object_id)=@tbl
and COL_NAME(parent_object_id,parent_column_id)=@col

END

GO

EXEC sys.sp_MS_marksystemobject sp_script_df
GO

References:

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s