Skripta tabellinnehåll

CREATE PROCEDURE DevUtil.Script_TableContents
  @Table varchar(300),
  @Where varchar(300) = NULL
AS
BEGIN

  SET NOCOUNT ON;

  DECLARE
    @Sql nvarchar(max),
    @TableName nvarchar(100),
    @SchemaName nvarchar(100),
    @ColumnNames nvarchar(max),
    @FirstColumnName nvarchar(max),
    @ColumnSelect nvarchar(max),
    @ColumnVariablesDeclaration nvarchar(max),
    @ColumnVariables nvarchar(max),
    @ColumnVariablesPrintSql nvarchar(max);

  SELECT
    @TableName = REVERSE(pvt.[1]),
    @SchemaName = COALESCE(REVERSE(pvt.[2]), 'dbo')
  FROM
    Util.String_Split(REVERSE(@Table), '.') ss
  PIVOT (
    MAX(ss.Txt)
    FOR Id IN ([1], [2])
  ) pvt

  SELECT
    @SchemaName = IIF(@SchemaName LIKE '[[]%]', @SchemaName, QUOTENAME(@SchemaName)),
    @TableName = IIF(@TableName LIKE '[[]%]', @TableName, QUOTENAME(@TableName));

  DECLARE
    @FullName nvarchar(300) = @SchemaName + '.' + @TableName;

  CREATE TABLE #Column
  (
    ColumnId int NOT NULL,
    ColumnName nvarchar(258) NOT NULL,
    VariableName varchar(13) NOT NULL,
    IsIdentity bit NOT NULL,
    TypeName sysname NOT NULL,
    UseQuotes bit NOT NULL,
    PrintSql varchar(300) NULL
  );

  INSERT INTO #Column
  (
    ColumnId,
    ColumnName,
    VariableName,
    IsIdentity,
    UseQuotes,
    TypeName
  )
  SELECT
    c.column_id,
    QUOTENAME(c.name),
    'var' + CONVERT(varchar(10), c.column_id),
    c.is_identity,
    CASE
      WHEN ty.name LIKE '%int' THEN
        0
      WHEN ty.name IN ('binary', 'varbinary', 'bit') THEN
        0
      ELSE
        1
    END,
    ty.name
  FROM
    sys.tables t
    JOIN sys.columns c ON
      c.object_id = t.object_id
    JOIN sys.types ty ON
      ty.system_type_id = c.system_type_id
  WHERE
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) = @FullName
  ORDER BY
    c.column_id;

  UPDATE
    #Column
  SET
    PrintSql =
      CASE
        WHEN UseQuotes = 0 THEN
          '@' + VariableName
        ELSE
          '''''''''' + ' + REPLACE(@' + VariableName + ', '''''''', '''''''''''') + '''''''''
      END;

  SELECT
    @ColumnNames = STUFF(
      (
        SELECT
          ', ' + c.ColumnName
        FROM
          #Column c
        ORDER BY
          c.ColumnId
        FOR XML PATH(''), TYPE
      ).value('.', 'varchar(max)'),
      1,
      2,
      ''
    ),
    @ColumnSelect = STUFF(
      (
        SELECT
          ',' +
            CASE
              WHEN c.TypeName IN ('binary', 'varbinary') THEN
                'CONVERT(varchar(max), ' + c.ColumnName + ', 1)'
              ELSE
                'CONVERT(varchar(max), ' + c.ColumnName + ')'
            END
        FROM
          #Column c
        ORDER BY
          c.ColumnId
        FOR XML PATH(''), TYPE
      ).value('.', 'varchar(max)'),
      1,
      1,
      ''
    ),
    @ColumnVariables = STUFF(
      (
        SELECT
          ',@' + c.VariableName
        FROM
          #Column c
        ORDER BY
          c.ColumnId
        FOR XML PATH(''), TYPE
      ).value('.', 'varchar(max)'),
      1,
      1,
      ''
    ),
    @ColumnVariablesPrintSql = STUFF(
      (
        SELECT
          ' + '', '' + IIF(@' + c.VariableName + ' IS NULL, ''NULL'', ' + c.PrintSql + ')'
        FROM
          #Column c
        ORDER BY
          c.ColumnId
        FOR XML PATH(''), TYPE
      ).value('.', 'varchar(max)'),
      1,
      10,
      ''
    );

  SELECT TOP 1
    @FirstColumnName = c.ColumnName
  FROM
    #Column c
  ORDER BY
    c.ColumnId;

  SELECT
    @ColumnVariablesDeclaration =
      REPLACE(@ColumnVariables, ',', ' varchar(max), ') + ' varchar(max)';


  PRINT 'DELETE ' + @FullName + COALESCE(CHAR(13) + CHAR(10) + 'WHERE ' + @Where, '') + ';';
  PRINT CHAR(13) + CHAR(10);

  IF EXISTS ( SELECT * FROM #Column c WHERE c.IsIdentity = 1)
  BEGIN
    PRINT 'SET IDENTITY_INSERT' + @FullName + ' ON;';
    PRINT CHAR(13) + CHAR(10);
  END

  SELECT @Sql = N'
  DECLARE RowCursor CURSOR LOCAL STATIC FORWARD_ONLY FOR
    SELECT
      ROW_NUMBER() OVER (ORDER BY ' + @FirstColumnName + ') RowNum,
      ROW_NUMBER() OVER (ORDER BY ' + @FirstColumnName + ' DESC) RowNumDesc,
      ' + @ColumnSelect + '
    FROM
      ' + @FullName +
    COALESCE(CHAR(13) + CHAR(10) + 'WHERE ' + @Where, '') + '
    ORDER BY
      ' + @FirstColumnName + ' ASC;

  DECLARE
    @RowNum int, @RowNumDesc int, ' + @ColumnVariablesDeclaration + ';

  OPEN RowCursor;

  WHILE 1 = 1
  BEGIN
    FETCH NEXT FROM RowCursor INTO @RowNum, @RowNumDesc, ' + @ColumnVariables + ';

    IF @@FETCH_STATUS <> 0
    BEGIN
      BREAK;
    END;

    IF (@RowNum % 1000 = 1)
    BEGIN

      PRINT ''INSERT INTO '' + ''' + @FullName + ''';
      PRINT ''  ('' + ''' + @ColumnNames + ''' + '')'';
      PRINT ''VALUES'';
    END

    PRINT ''  ('' + ' + @ColumnVariablesPrintSql + ' + '')'' + IIF(@RowNumDesc > 1 AND @RowNum % 1000 > 0, '','', '';'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10))
  END;

  CLOSE RowCursor;
  DEALLOCATE RowCursor;';

  --PRINT @Sql
  EXEC sp_executesql @Sql;

  IF EXISTS ( SELECT * FROM #Column c WHERE c.IsIdentity = 1)
  BEGIN
    PRINT 'SET IDENTITY_INSERT' + @FullName + ' OFF;';
  END

END