Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
tSQLt prepare table for faking
/*
Source: http://harouny.com/2013/04/19/tsqlt-taketable-indexed-view/
Description: With small modifications on REPLACE part and code style improvments
*/
CREATE PROCEDURE [tSQLt].[PrepareTableForFaking]
@TableName NVARCHAR(MAX),
@SchemaName NVARCHAR(MAX)
AS
BEGIN
--remove brackets
SELECT @TableName = PARSENAME(@TableName,1)
SELECT @SchemaName = PARSENAME(@SchemaName,1)
-- delete temptable
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#temp'))
BEGIN
DROP TABLE #TEMP
END
--recursively get all referencing dependencies
;WITH ReferencedDependencies (parentId, name, LEVEL)
AS(
SELECT DISTINCT o.object_id AS parentId, o.name, 0 AS LEVEL
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o
ON d.referencing_id = o.object_id
AND o.type IN ('FN','IF','TF', 'V', 'P')
AND is_schema_bound_reference = 1
WHERE
d.referencing_class = 1 AND referenced_entity_name = @TableName AND referenced_schema_name = @SchemaName
UNION ALL
SELECT o.object_id AS parentId, o.name, LEVEL +1
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o
ON d.referencing_id = o.object_id
AND o.type IN ('FN','IF','TF', 'V', 'P')
AND is_schema_bound_reference = 1
JOIN ReferencedDependencies AS RD
ON d.referenced_id = rd.parentId
)
-- select all objects referencing this table in reverse level order
SELECT DISTINCT IDENTITY(INT, 1,1) AS id, name, OBJECT_DEFINITION(parentId) as obj_def, parentId as obj_Id , LEVEL
INTO #TEMP
FROM ReferencedDependencies
WHERE OBJECT_DEFINITION(parentId) LIKE '%SCHEMABINDING%'
ORDER BY LEVEL DESC
OPTION (Maxrecursion 1000);
--prepere the query to remove all dependent indexes (this is nessesary to removing (with schemabinding) later)
DECLARE @qryRemoveIndexes NVARCHAR(MAX);
SELECT @qryRemoveIndexes = (
SELECT 'DROP INDEX ' + i.name + ' ON ' + OBJECT_NAME(o.id) + '; ' FROM sys.sysobjects AS o
INNER JOIN #TEMP ON o.id = #TEMP.obj_Id
INNER JOIN sys.sysindexes AS i ON i.id = o.id
WHERE i.indid = 1 -- 1 = Clustered index (we are only interested in clusterd indexes)
FOR XML PATH(''));
--excute @qryRemoveIndexes
EXEC sp_executesql @qryRemoveIndexes;
--change the definition for removing (with schemabinding) from those objects
DECLARE @currentRecord INT
DECLARE @qryRemoveWithSchemabinding NVARCHAR(MAX)
SET @currentRecord = 1
WHILE (@currentRecord <= (SELECT COUNT(1) FROM #TEMP) )
BEGIN
SET @qryRemoveWithSchemabinding = ''
SELECT @qryRemoveWithSchemabinding = #TEMP.obj_def
FROM #TEMP
WHERE #TEMP.id = @currentRecord
-- TODO: Rafactor, this part is writen in very optimistic way ;)
SET @qryRemoveWithSchemabinding = REPLACE(@qryRemoveWithSchemabinding,'CREATE ', 'ALTER ')
SET @qryRemoveWithSchemabinding = REPLACE(@qryRemoveWithSchemabinding,'with schemabinding', ''); -- remove schema binding
-- execute @qryRemoveWithSchemabinding
-- PRINT @qryRemoveWithSchemabinding
EXEC sp_executeSQL @qryRemoveWithSchemabinding;
SET @currentRecord = @currentRecord + 1
END
END
GO
-- Usage example:
EXEC tSQLt.PrepareTableForFaking @TableName='table', @SchemaName='dbo';
@DaveBoltman

This comment has been minimized.

Copy link

DaveBoltman commented May 28, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.