Yet Another SQL Extraction Script

Another script folks, this time a little bit more advanced than the last. This one generates the scripts you need and gives you ome output so you can see what’s going on. I find it very useful for deployment purposes.

SET NOCOUNT ON;
/*
       This function strips the comments out of an SQL routine to allow analysis of
       whether or not it references another routine without worrying about comment
       references, e.g. "This function acts like BizAllAccess.fGetInceptionDate but
       it uses a different return type" or comments of this nature will not trigger
       a dependency.
*/
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(
       N'[Intern].[fStripComments]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [Intern].[fStripComments]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Intern].[fStripComments] (
       @routine_definition     NVARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS BEGIN

       DECLARE @sqlText                NVARCHAR(MAX);
       DECLARE @commentLine    NVARCHAR(MAX);
       DECLARE @beginComment   INT;
       DECLARE @endComment             INT;
       SET     @sqlText = @routine_definition;

       SELECT  @beginComment   =       PATINDEX('%--%', @sqltext);
       WHILE @beginComment  0 BEGIN
               SELECT  @commentline    =       SUBSTRING(@sqlText, @beginComment, LEN(@sqlText));
               SELECT  @endComment             =       CHARINDEX(CHAR(10), @commentLine) + 1;
               SELECT  @commentLine    =       SUBSTRING(@commentLine, 1, @endComment);
               SELECT  @sqlText                =       REPLACE(@sqlText, @commentLine, '');
               SELECT  @beginComment   =       PATINDEX('%--%', @sqltext);
       END

       SELECT  @beginComment   =       PATINDEX('%/*%', @sqltext);
       WHILE @beginComment  0 BEGIN
               SELECT  @commentline    =       SUBSTRING(@sqlText, @beginComment, LEN(@sqlText));
               SELECT  @endComment             =       PATINDEX('%*/%', @commentLine) + 1;
               SELECT  @commentLine    =       SUBSTRING(@commentLine, 1, @endComment);
               SELECT  @sqlText                =       REPLACE(@sqlText, @commentLine, '');
               SELECT  @beginComment   =       PATINDEX('%/*%', @sqltext);
       END

       RETURN @sqlText;

END
GO
/*
       Declaration of variables to affect processing

*/
DECLARE @SelectSchemaName                               NVARCHAR(MAX);
DECLARE @SelectObjectName                               NVARCHAR(MAX);

/* Schema-qualified name of the object to do analysis on */
DECLARE @SelectDependentObjectName              NVARCHAR(MAX);


SET     @SelectDependentObjectName = 'BizNewReportingAccess.fGetPerformanceValue';


DECLARE @SelectedObjectNames    TABLE(
       DependentObjectName                     NVARCHAR(MAX)
);

DECLARE @SelectObjectType               TABLE (
       obj_type CHAR(2) COLLATE Latin1_General_CI_AS_KS_WS
,       obj_type_literal NVARCHAR(MAX)
);

SET     @SelectSchemaName = NULL;
SET     @SelectObjectName = NULL;


INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('AF',    'AGG_FUNC_CLR');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('C',             'CHECK_CONSTRAINT ');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('D',             'DEFAULT');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('F',             'FOREIGN_KEY');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('FN',    'SCALAR_FUNCTION_SQL');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('FS',    'SCALAR_FUNCTION_CLR');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('FT',    'TABLE_FUNCTION_CLR');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('IF',    'TABLE_FUNCTION_INLINE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('IT',    'INTERNAL_TABLE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('P',             'STORED_PROC');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('PC',    'STORED_PROC_CLR');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('PG',    'PLAN_GUIDE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('PK',    'PRIMARY_KEY');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('R',             'RULE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('RF',    'REPLICATION_FILTER_PROCEDURE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('S',             'SYSTEM_BASE_TABLE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('SN',    'SYNONYM');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('SQ',    'SERVICE_QUEUE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('TA',    'DML_TRIGGER_CLR');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('TF',    'TABLE_FUNCTION_SQL');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('TR',    'DML_TRIGGER_SQL');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('TT',    'TABLE_TYPE');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('U',             'TABLE_USER_DEFINED');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('UQ',    'UNIQUE_CONSTRAINT');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('V',             'VIEW');
INSERT INTO @SelectObjectType(  obj_type, obj_type_literal)
VALUES('X',             'EXTPROC');



/*
               WHEN 'AF'       THEN 'AGG_FUNC_CLR'                                     -- Aggregate function (CLR)
        WHEN 'C'       THEN 'CHECK_CONSTRAINT'                         -- CHECK constraint
        WHEN 'D'       THEN 'DEFAULT'                                          -- DEFAULT (constraint or stand-alone)
        WHEN 'F'       THEN 'FOREIGN_KEY'                                      -- FOREIGN KEY constraint
        WHEN 'FN'      THEN 'SCALAR_FUNCTION_SQL'                      -- SQL scalar function
        WHEN 'FS'      THEN 'SCALAR_FUNCTION_CLR'                      -- Assembly (CLR)
scalar-function
        WHEN 'FT'      THEN 'TABLE_FUNCTION_CLR'                       -- Assembly (CLR)
table-valued function
        WHEN 'IF'      THEN 'TABLE_FUNCTION_INLINE'            -- SQL inline
table-valued function
        WHEN 'IT'      THEN 'INTERNAL_TABLE'                           -- Internal table
        WHEN 'P'       THEN 'STORED_PROC'                                      -- SQL Stored Procedure
        WHEN 'PC'      THEN 'STORED_PROC_CLR'                          -- Assembly (CLR) stored-procedure
        WHEN 'PG'      THEN 'PLAN_GUIDE'                                       -- Plan guide
        WHEN 'PK'      THEN 'PRIMARY_KEY'                                      -- PRIMARY KEY constraint
        WHEN 'R'       THEN 'RULE'                                                     -- Rule (old-style, stand-alone)
        WHEN 'RF'      THEN 'REPLICATION_FILTER_PROCEDURE'     --
Replication-filter-procedure
        WHEN 'S'       THEN 'SYSTEM_BASE_TABLE'                        -- System base table
        WHEN 'SN'      THEN 'SYNONYM'                                          -- Synonym
        WHEN 'SQ'      THEN 'SERVICE_QUEUE'                            -- Service queue
        WHEN 'TA'      THEN 'DML_TRIGGER_CLR'                          -- Assembly (CLR) DML trigger
        WHEN 'TF'      THEN 'TABLE_FUNCTION_SQL'                       -- SQL table-valued-function
        WHEN 'TR'      THEN 'DML_TRIGGER_SQL'                          -- SQL DML trigger
        WHEN 'TT'      THEN 'TABLE_TYPE'                                       -- Table type
        WHEN 'U'       THEN 'TABLE_USER_DEFINED'                       -- Table (user-defined)
        WHEN 'UQ'      THEN 'UNIQUE_CONSTRAINT'                        -- UNIQUE constraint
        WHEN 'V'       THEN 'VIEW'                                                     -- View
        WHEN 'X'       THEN 'EXTPROC'                                          -- Extended stored procedure
        ELSE 'UNKNOWN'

*/
DECLARE
       @ObjectName                             NVARCHAR(MAX)
,       @ObjectType                             NVARCHAR(MAX)
,       @SchemaName                             NVARCHAR(MAX)
,       @SchemaId                               NVARCHAR(MAX)
,       @ObjectFullName                 NVARCHAR(MAX)
,       @ObjectId                               INT
,       @SortOrder                              INT
,       @CommandCursor                  CURSOR
,       @object_definition              NVARCHAR(MAX)
,       @block                                  NVARCHAR(MAX)
,       @printblocksize                 INT
,       @lengthleft                             INT
,       @lengthtoprint                  INT
,       @crlf                                   NVARCHAR
,       @constraint_full_name   NVARCHAR(MAX)
,       @constraint_name                NVARCHAR(MAX)
,       @table_full_name                NVARCHAR(MAX)
,       @constraint_definition  NVARCHAR(MAX)
;

DECLARE @Objects TABLE(
       ObjectId   INT
,       SchemaId   INT
,       SchemaName NVARCHAR(MAX)
,       ObjectType NVARCHAR(40)
,       ObjectName NVARCHAR(MAX)
,       ObjectCode NVARCHAR(MAX)
);

-- grab all out objects and their text
INSERT INTO @Objects
SELECT
       so.object_id
,       so.schema_id
,       SCHEMA_NAME(so.schema_id) AS schema_name
,       obj_type_literal
,       so.[Name]
,       Intern.fStripComments(sc.[Text])
FROM
                       sys.objects so WITH (NOLOCK)
INNER JOIN      syscomments sc ON so.object_id = sc.id
INNER JOIN      (SELECT * FROM @SelectObjectType) objtype  ON
objtype.obj_type = [Type]
WHERE
               so.object_id = sc.id
AND ((  @SelectSchemaName IS NULL) OR (SCHEMA_NAME(schema_id) =
@SelectSchemaName))
AND ((  @SelectObjectName IS NULL) OR (SCHEMA_NAME(schema_id) + '.' +
[Name] = @SelectObjectName))
ORDER  BY so.name ASC;

DECLARE @Dependencies TABLE (
       DependentId             INT
,       DependentName   NVARCHAR(MAX)
,       DependsOnId             INT
,       DependsOnName   NVARCHAR(MAX)
);
DECLARE @DependencyAnalysis TABLE (
       DependentId             INT
,       DependentName   NVARCHAR(MAX)
,       DependsOnId             INT
,       DependsOnName   NVARCHAR(MAX)
,       Depth                   INT
,       MaxDepth                INT
);

/*      analyse the text of all the objects to determine who needs who
       We end up with a list of all objects which depend on others
*/
INSERT INTO @Dependencies
SELECT
       a.ObjectId                                                              AS [DependentId]
,       a.SchemaName + '.' + a.ObjectName               AS [DependentName]
,       b.ObjectId                                                              AS [DependsOnId]
,       b.SchemaName + '.' + b.ObjectName               AS [DependsOnName]
FROM
       @Objects a
,       @Objects b
WHERE
       ( a.ObjectName  b.ObjectName )
AND     CHARINDEX(UPPER(b.ObjectName), UPPER(a.ObjectCode))  0
--AND (@SelectDependentObjectName = NULL OR a.SchemaName + '.' +
a.ObjectName = @SelectDependentObjectName)
;
--SELECT * FROM @Dependencies;

/*      The following CTE is used to build a graph of which are the deepest
dependencies */
;WITH dep_cte   AS (
       /*
               Set of top-level dependencies
       */
       SELECT
               DependentId             AS DependentId
       ,       DependentName   AS DependentName
       ,       DependsOnId             AS DependsOnId
       ,       DependsOnName   AS DependsOnName
       ,       1                               AS Depth
       FROM
               @Dependencies a
       WHERE
               a.DependentName = @SelectDependentObjectName
),analysis_cte  AS (
       SELECT
               c.DependentId   AS DependentId
       ,       c.DependentName AS DependentName
       ,       c.DependsOnId   AS DependsOnId
       ,       c.DependsOnName AS DependsOnName
       ,       c.Depth                 AS Depth
       FROM
               dep_cte c
       UNION ALL
       SELECT
               d.DependentId   AS DependentId
       ,       d.DependentName AS DependentName
       ,       d.DependsOnId   AS DependsOnId
       ,       d.DependsOnName AS DependsOnName
       ,       c.Depth + 1             AS Depth
       FROM
               @Dependencies d
       INNER JOIN analysis_cte c
               ON c.DependsOnId = d.DependentId
       WHERE c.Depth < 100
)


INSERT INTO @DependencyAnalysis
SELECT
       c.DependentId   AS DependentId
,       c.DependentName AS DependentName
,       c.DependsOnId   AS DependsOnId
,       c.DependsOnName AS DependsOnName
,       c.Depth                 AS Depth
,       MAX(c.Depth) OVER (PARTITION BY c.DependentID)
                                       AS MaxDepth
FROM   analysis_cte c
GROUP  BY
       DependentId
,       DependentName
,       DependsOnId
,       DependsOnName
,       Depth
ORDER  BY
       Depth
;

DECLARE @Hierarchy TABLE(
       ObjectID                INT
,       ObjectName              NVARCHAR(MAX)
,       Rank                    INT
,       DependsOn               NVARCHAR(MAX)
);
INSERT INTO @Hierarchy
SELECT DependentID, DependentName, Depth , NULL
FROM(
       SELECT          DependentID, DependentName, Depth, MaxDepth
       FROM            @DependencyAnalysis
       GROUP BY        DependentID, DependentName, Depth, MaxDepth
       HAVING          Depth = MaxDepth
) a
SELECT * FROM @Hierarchy ORDER BY Rank;
SELECT * FROM @Hierarchy ORDER BY Rank DESC;


DECLARE @drop_cursor CURSOR;
-- we drop objects from the top of the dependency tree downward
SET @drop_cursor = CURSOR FAST_FORWARD FOR SELECT ObjectId, ObjectName
FROM @Hierarchy ORDER BY Rank
DECLARE @drop_id INT, @drop_name NVARCHAR(MAX);
OPEN @drop_cursor
FETCH NEXT FROM @drop_cursor INTO @drop_id, @drop_name
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @obj_type NVARCHAR(MAX);


       SELECT @obj_type = type_desc from sys.objects where object_id = @drop_id;

       IF @obj_type IN ('SQL_STORED_PROCEDURE') BEGIN
               PRINT 'IF EXISTS (select * from dbo.sysobjects ';
               PRINT '    WHERE ID = object_id(N' + char(39) + @drop_name + char(39) + ')';
               PRINT '    AND OBJECTPROPERTY(id, N' + char(39) + 'IsProcedure' +
char(39) + ') = 1) ';
               PRINT 'DROP PROCEDURE ' + @drop_name  ;
               PRINT 'GO '
               PRINT ' '
       END

       IF @obj_type IN (
               'SQL_SCALAR_FUNCTION'
       ,       'SQL_INLINE_TABLE_VALUED_FUNCTION'
       ,       'SQL_TABLE_VALUED_FUNCTION') BEGIN

               PRINT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''
                       +       @drop_name
                       +       ''') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))'
               PRINT '    DROP FUNCTION ' + @drop_name
               PRINT 'GO '
               PRINT ' '

       END

       IF @obj_type IN ('VIEW') BEGIN
               PRINT 'GO';
               PRINT 'IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'''
                       +       @drop_name + '''))'
               PRINT '    DROP VIEW ' + @drop_name
               PRINT 'GO '
               PRINT ' '
       END


       FETCH NEXT FROM @drop_cursor INTO @drop_id, @drop_name
END
CLOSE           @drop_cursor;
DEALLOCATE      @drop_cursor;


DECLARE @create_cursor CURSOR;
-- we create objects from the bottom of the dependency tree upward
SET @create_cursor = CURSOR FAST_FORWARD FOR SELECT ObjectId,
ObjectName FROM @Hierarchy ORDER BY Rank DESC
DECLARE @create_id INT, @create_name NVARCHAR(MAX);
OPEN @create_cursor
FETCH NEXT FROM @create_cursor INTO @create_id, @create_name
WHILE @@FETCH_STATUS = 0 BEGIN
       DECLARE @obj_definition NVARCHAR(MAX);
       SELECT  @obj_definition = object_definition(@create_id);
       WHILE @obj_definition  '' BEGIN
               PRINT LEFT(@obj_definition, 4000);
               SET @obj_definition     = SUBSTRING(@obj_definition, 4001, LEN(@obj_definition));
       END

       PRINT '  ';
       PRINT 'GO ';
       PRINT 'SET QUOTED_IDENTIFIER OFF ';
       PRINT 'GO ';
       PRINT 'SET ANSI_NULLS OFF ' ;
       PRINT 'GO';
       PRINT ' ';
       PRINT ' ';
       FETCH NEXT FROM @create_cursor INTO @create_id, @create_name
END
CLOSE           @create_cursor;
DEALLOCATE      @create_cursor;
Advertisements

About this entry