tSQLt prepare table for faking
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
See some improvements here https://gist.github.com/DaveBoltman/2ad19779ccd5f95dd4a7a5d47fe914cb