Spalten ohne Varianzen

[Mokus]

Hey hoy,

hab ein kleines SQL Statement geschribene,w elches alle Spalten ohne Varianz ausgegen soll ...

Problem ?
Fucking performance......

wenn wer auf das Script schaut und verbesserung habt, bitte melden ....

SQL-Code:

/* 
@Result vom dynamischen sql: 
0 = NUR NULL WERTE
1 = VERSCHIEDENE WERTE
2 = EIN EINZIGER WERT DER NICHT NULL IST

*/

alter procedure GET_SPALTEN
as
begin
  set NOCOUNT on

  declare @Tab table (
    ID       INT identity(0, 1) primary key,
    TAB      NVARCHAR(MAX),
    SPALTE   NVARCHAR(MAX),
    TYP      NVARCHAR(MAX),
    WERT     NVARCHAR(MAX),
    ONLYNULL BIT,
    ROW      INT)

  declare @tabelle NVARCHAR(MAX)

  declare @debug INT = 0

  declare @Spalte NVARCHAR(MAX)

  declare @Typ NVARCHAR(MAX)

  declare @vorhanden SMALLINT

  declare @sql NVARCHAR(MAX)

  declare @params NVARCHAR(MAX)

  declare @einzigWert NVARCHAR(MAX)

  declare COURCHECK cursor FAST_FORWARD for
    (select
       TAB,
       SPALTE,
       TYP
     from   @Tab)

  insert into @Tab
              (TAB,
               SPALTE,
               TYP)
  (select
     SYS.TABLES.NAME,
     SYS.ALL_COLUMNS.NAME,
     SYS.TYPES.NAME
   from   SYS.TABLES
          join SYS.ALL_COLUMNS
            on SYS.ALL_COLUMNS.OBJECT_ID = SYS.TABLES.OBJECT_ID
          join SYS.TYPES
            on SYS.TYPES.USER_TYPE_ID = SYS.ALL_COLUMNS.USER_TYPE_ID
   where  SYS.TABLES.TYPE = 'U')

  select
    NAME=SPACE(100),
    ROWS = 1000000,
    RESERVED=SPACE(255),
    DATA=SPACE(255),
    INDEX_SIZE=SPACE(255),
    UNUSED= SPACE(255)
  into   #TEMP
  -- dmv 
  delete #TEMP

  declare @name VARCHAR(255)

  declare CRSTOUR  cursor fast_forward for
    select
      NAME
    from   SYSOBJECTS
    where  XTYPE = 'U' --order by name

  open CRSTOUR

  fetch NEXT from CRSTOUR into @name

  while ( @@fetch_status = 0 )
  begin
    insert into #TEMP
    execute SP_SPACEUSED
      @name

    fetch NEXT from CRSTOUR into @name
  end

  close CRSTOUR

  deallocate CRSTOUR

  update @Tab
  set    ROW = #TEMP.ROWS
  from   @Tab as G
         join #TEMP
           on G.TAB = #TEMP.NAME

  delete @Tab
  where  ROW = 0

  open COURCHECK

  fetch NEXT from COURCHECK into @tabelle, @Spalte, @Typ

  while @@fetch_status = 0
  begin
    set @sql = N' if exists( select top 1 1 from dbo.'
               + @tabelle + ' where ' + @Spalte
               + ' is not null) 
  begin 
        if exists (select min(' + @Spalte
               + ') from dbo.' + @tabelle + ' having min('
               + @Spalte + ') <> max(' + @Spalte + ')) 
        begin
        set @Result = 1   
        end
        else
        begin 
        set @result = 2
        end      
  end 
  else
  begin 
  set @result = 0
  end'

    set @params = N'@Result smallint OUTPUT @wert NVARCHAR(MAX) OUTPUT'

    exec SP_EXECUTESQL
      @sql,
      @params,
      @result = @vorhanden OUTPUT

    if @vorhanden = 1
    begin
      delete @Tab
      where  TAB = @tabelle
         and SPALTE = @Spalte
    end

    if @vorhanden = 0
    begin
      set @debug = @debug + 1
    end

    /*    if @vorhanden = 0
        begin
          update @Tab
          set    ONLYNULL = 1
          where  TAB = @tabelle
             and @Spalte = SPALTE
        end
       */
    if @vorhanden = 2
    begin
      set @sql = N' select top 1 @wert = cast(' + @Spalte
                 + ' as NVARCHAR(MAX) ) from dbo.' + @tabelle

      set @params = N'@wert NVARCHAR(MAX) OUTPUT'

      exec SP_EXECUTESQL
        @sql,
        @params,
        @wert = @einzigWert OUTPUT

      update @Tab
      set    WERT = @einzigWert
      where  @tabelle = TAB
         and @Spalte = SPALTE
    end

    fetch NEXT from COURCHECK into @tabelle, @Spalte, @Typ
  end

  close COURCHECK

  select
    *
  from   @Tab

end

dank im vorraus