Estoy escribiendo una declaración SQL dinámica que verificará si el índice existe y lo eliminará. Obteniendo un error de compilación ¿Alguien podría decir cuál es el problema? Revisé dos veces las garrapatas pero no puedo entender

declare @startyear int = 2000
declare @startQuarter int = 1
declare @sql nvarchar(max)
declare @tableName varchar(50)

if @startYear is null
    set @startYear = 2000;
set @startQuarter = 1;
while @startYear <= year(getdate())
    begin
        set @startQuarter = 1;

        while @startQuarter < 5
        begin
            set @tableName = 'FinData' + cast(@startYear as varchar) + '_' + cast(@startQuarter as varchar);
            set @sql = 'IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id(' + @tableName + ') AND NAME = ' + '.idx_' + @tableName 
            drop  index' + @tableName + '.idx_' + @tableName 

            print  @sql

            set @startQuarter += 1
        end
        set @startYear += 1;

    end
0
Tom 14 nov. 2017 a las 21:36

2 respuestas

La mejor respuesta

Dos problemas: esto no agrega nada antes del .idx, solo concatena las cadenas:

') AND NAME = ' + '.idx_'

Y luego falta una comilla simple al final de esa línea. Parece que sus índices tienen el nombre del nombre de la tabla: tablename.idx_tablename, así que intente esto:

DECLARE @startyear INT = 2000;
DECLARE @startQuarter INT = 1;
DECLARE @sql NVARCHAR(MAX);
DECLARE @tableName VARCHAR(50);

IF @startyear IS NULL
    SET @startyear = 2000;
SET @startQuarter = 1;
WHILE @startyear <= YEAR(GETDATE())
BEGIN
    SET @startQuarter = 1;

    WHILE @startQuarter < 5
    BEGIN
        SET @tableName = 'FinData' + CAST(@startyear AS VARCHAR) + '_'
            + CAST(@startQuarter AS VARCHAR);
        SET @sql = 'IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('
            + @tableName + ') AND NAME = ' + @tableName + '.idx_' + @tableName + ' 
            drop  index' + @tableName + '.idx_' + @tableName;

        PRINT @sql;

        SET @startQuarter += 1;
    END;
    SET @startyear += 1;

END;
0
Russell Fox 14 nov. 2017 a las 18:43

Esto no responde a tu pregunta, sin embargo, vale la pena mencionar que si quieres hacer esto sin bucle, puedes crear un tabla de números virtuales para unirse.

;WITH 
A AS(SELECT 0 AS Q UNION ALL SELECT 0), 
B AS(SELECT 0 AS Q FROM A AS A CROSS JOIN A AS B),
C AS(SELECT 0 AS Q FROM B AS A CROSS JOIN B AS B),
D AS(SELECT 0 AS Q FROM C AS A CROSS JOIN C AS B),
E AS(SELECT 0 AS Q FROM D AS A CROSS JOIN D AS B),
F AS(SELECT 0 AS Q FROM E AS A CROSS JOIN E AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM F)

SELECT 
    Y.Number,Q.Number
FROM 
    Numbers Y
    INNER JOIN Numbers Q ON Q.Number BETWEEN 1 AND 4
WHERE
    Y.Number BETWEEN 2000 AND YEAR(GETDATE())
ORDER BY 
    Y.Number
0
Ross Bush 14 nov. 2017 a las 19:06