lunes, 30 de diciembre de 2013

SQL SERVER: BUSCANDO TEXTO EN TABLAS.

BUSCANDO TEXTO EN... ¿TODAS LAS TABLAS?...

Hace ya algunas semanas -  ¿meses quizá? - me vi en la penosa necesidad de rastrear datos por una base de datos algo grande. El contexto es en sí es irrelevante. Lo interesante era buscar esos datos, un grupo de cadenas de texto y ver con que campos de que tablas se estaban relacionando o los estaban conteniendo para un posterior análisis. En medio de la tarea me topé con un procedimiento almacenado diseñado por Narayana Vyas Kondreddi algo interesante, y funcional.... Pero, había un detalle... El procedimiento busca literalmente en todas las tablas de la base de datos, lo que, es en sí bueno si: 

 1) No tienes la más remota idea de dónde empezar a buscar.
 2) La base de datos es pequeña o a pesar de contener muchas tablas estas no tienen una cantidad abismal de registros.

Para mi bien ya tenía una lista depurada de tablas donde debía de buscar por este grupo de datos. Por lo que procedí a adaptar el procedimiento almacenado en algo que pudiera especificar. El procedimiento original lo pueden encontrar en http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm, en su momento lo adapte para un grupo de tablas específicas y quedo, a modo de ejemplo, el siguiente query:

DECLARE @SearchStr NVARCHAR(100) = 'Bike'

BEGIN
  SET NOCOUNT ON

  DECLARE @TABLAS TABLE (
    Tabla CHAR(45)
    ,N TINYINT IDENTITY
    )
  DECLARE @TMP VARCHAR(20) = NULL
  DECLARE @Ciclos TINYINT = 0

  INSERT INTO @TABLAS
  VALUES ('[dbo].[dimreseller]')

  INSERT INTO @TABLAS
  VALUES ('[dbo].[dimaccount]')

  INSERT INTO @TABLAS
  VALUES ('[dbo].[dimdat]')

  SET @Ciclos = (
      SELECT MAX(N)
      FROM @TABLAS
      )

  IF OBJECT_ID('tempdb..#Results') IS NOT NULL
  BEGIN
    DROP TABLE #Results
  END

  CREATE TABLE #Results (
    ColumnName NVARCHAR(370)
    ,ColumnValue NVARCHAR(3630)
    )

  DECLARE @TableName NVARCHAR(256)
    ,@ColumnName NVARCHAR(128)
    ,@SearchStr2 NVARCHAR(110)

  SET @TableName = ''
  SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

  WHILE (@Ciclos > 0)
  BEGIN
    SET @ColumnName = ''
    SET @TableName = (
        SELECT REPLACE(Tabla, ' ', '')
        FROM @TABLAS
        WHERE N = @Ciclos
        )

    WHILE (@TableName IS NOT NULL)
      AND (@ColumnName IS NOT NULL)
    BEGIN
      SET @ColumnName = (
          SELECT MIN(QUOTENAME(COLUMN_NAME))
          FROM INFORMATION_SCHEMA.COLUMNS(NOLOCK)
          WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
            AND TABLE_NAME = PARSENAME(@TableName, 1)
            AND DATA_TYPE IN (
              'char'
              ,'varchar'
              ,'nchar'
              ,'nvarchar'
              )
            AND QUOTENAME(COLUMN_NAME) > @ColumnName
          )

      IF @ColumnName IS NOT NULL
      BEGIN
        INSERT INTO #Results
        EXEC (
            'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                           FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
      END
    END

    SET @Ciclos = @Ciclos - 1
  END

  SELECT DISTINCT ColumnName AS [TABLA/COLUMNA]
    ,ColumnValue AS [VALOR]
  FROM #Results

  DROP TABLE #Results

  SET NOCOUNT OFF
END

Funcional en su momento pero nada elegante. Igual, hace unos días recordé el caso y me gustó la idea de crear algo más pulido y que me permitiera contemplar los dos escenarios, es decir, tanto tener la posibilidad de buscar en todas las tablas de la base de datos y también la de especificar un grupo de tablas. Aunque interesante la idea de buscar en toda la base de datos me resulta un poco problemático si el servidor no tiene mucha potencia o si la base de datos es descomunalmente grande. Para bases pequeñas o medianas si me resulta algo practico. En fin, total que al final quedo el siguiente procedimiento almacenado:

CREATE PROCEDURE BuscarTextoEnTablas (
  @TMP NVARCHAR(MAX)
  ,@Print BIT = 0
  )
AS
/*
Autor:Rodrigo Anael Perez Castro | rapcx1981@gmail.com
Creado:30/12/2013 | Ultima Actualizacion:N/A

SINTAXIS:    
-Si se buscara el texto en todas las tablas:
  BuscarTextoEnTablas '<Texto a Buscar>'  Ej: BuscarTextoEnTablas 'A Bike Store',1
-Si se buscara el texto en un grupo especifico de tablas:
  BuscarTextoEnTablas '<Texto a Buscar> IN <Tabla 1>,<Tabla 2>, <Tabla N>' Ej:    BuscarTextoEnTablas 'A Bike Store IN dimreseller,dimaccount,dimdate'
                         
-Opcionalmente se puede especificar 1 despues del texto para indicar si se requiere mostrar el query generado en la pestaña de "Mensajes". Si no se indica nada automáticamente asume que no se requiere.    
                                                    
NOTAS:       -Basado en la idea y trabajo de Narayana Vyas Kondreddi [SearchAllTables/http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm]              
*/
BEGIN
  --VARIABLES DE ENTORNO--------------------------------------------------------
  DECLARE @Query NVARCHAR(MAX) = NULL

  SET @Query = N'
--VARIABLES DE ENTORNO--------------------------------------------------------
DECLARE @Buscar VARCHAR(MAX)=NULL
DECLARE @Ciclos TINYINT=0'
  --REFERENCIAS: http://sqlservercodebook.blogspot.mx/2008/03/check-if-temporary-table-exists.html
  SET @Query = @Query + '
IF OBJECT_ID(''tempdb..#Results'') IS NOT NULL
       BEGIN
             DROP TABLE #Results
       END

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

SET NOCOUNT ON'

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @Buscar=QUOTENAME(''%'' +(SELECT LTRIM(RTRIM(SUBSTRING(''' + @TMP + ''',0,(CHARINDEX(''IN'',''' + @TMP + ''')-1)))))+ ''%'','''''''')'
    --SEPARACION DE CADENA PARA FORMAR TABLA---------------------------------------------------------
    --BASADO EN LA FUNCION: http://emmersonmiranda-net.blogspot.mx/2008/08/generando-mltiples-filas-de-un-string.html
    --Writen By: Emmerson Miranda
    SET @Query = @Query + 'DECLARE @Tablas TABLE (Tabla NVARCHAR(75),N TINYINT IDENTITY)
             DECLARE @Array VARCHAR(MAX)=(SELECT LTRIM(RTRIM((SELECT SUBSTRING(''' + @TMP + ''',(SELECT CHARINDEX(''IN'',''' + @TMP + ''')+2),LEN(''' + @TMP + '''))))))
             DECLARE @SeparatorPosition INT=0
             DECLARE @ArrayValue VARCHAR(MAX)=NULL

             SET @Array = @Array + '',''

             WHILE PATINDEX(''%'' + '','' + ''%'' , @Array) <> 0
             BEGIN
                    SET @SeparatorPosition = PATINDEX(''%'' + '','' + ''%'' , @Array)
                    SET @ArrayValue = SUBSTRING(@Array, 0, @SeparatorPosition)
                    SET @Array = STUFF(@Array, 1, @SeparatorPosition, '''')
                    INSERT INTO @Tablas SELECT ''[dbo].[''+@ArrayValue+'']'' AS Tabla
             END
             '
      --SEPARACION DE CADENA PARA FORMAR TABLA---------------------------------------------------------
  END
  ELSE
  BEGIN
    SET @Query = @Query + '
             SET @Buscar=QUOTENAME(''%'' +(LTRIM(RTRIM(''' + @TMP + ''')))+ ''%'','''''''')'
  END

  SET @Query = @Query + '
             DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128)
             SET  @TableName = '''''

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @Ciclos=(SELECT MAX(N) FROM @Tablas)
             WHILE (@Ciclos>0)'
  END
  ELSE
  BEGIN
    SET @Query = @Query + '
             WHILE @TableName IS NOT NULL'
  END

  SET @Query = @Query + '
             BEGIN
             SET @ColumnName = '''''

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @TableName =(SELECT REPLACE(Tabla,'' '', '''')  FROM @Tablas WHERE N=@Ciclos)'
  END
  ELSE
  BEGIN
    SET @Query = @Query + '
             SET @TableName =
             (
                    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME))
                    FROM   INFORMATION_SCHEMA.TABLES
                    WHERE        TABLE_TYPE = ''BASE TABLE''
                           AND    QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) > @TableName
                           AND    OBJECTPROPERTY(
                                        OBJECT_ID(
                                               QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)
                                                ), ''IsMSShipped''
                                               ) = 0
             )'
  END

  SET @Query = @Query + 
    '      
             WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
             BEGIN
                    SET @ColumnName =
                    (
                           SELECT MIN(QUOTENAME(COLUMN_NAME))
                           FROM   INFORMATION_SCHEMA.COLUMNS
                           WHERE        TABLE_SCHEMA = PARSENAME(@TableName, 2)
                                  AND    TABLE_NAME   = PARSENAME(@TableName, 1)
                                  AND    DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
                                  AND    QUOTENAME(COLUMN_NAME) > @ColumnName
                    )
     
                    IF @ColumnName IS NOT NULL
                    BEGIN
                           INSERT INTO #Results
                           EXEC
                           (
                                  ''SELECT '''''' + @TableName + ''.'' + @ColumnName + '''''', LEFT('' + @ColumnName + '', 3630)
                                  FROM '' + @TableName + '' (NOLOCK) '' +
                                  '' WHERE '' + @ColumnName + '' LIKE '' + @Buscar
                           )
                    END
             END'

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @Ciclos = @Ciclos-1'
  END

  SET @Query = @Query + '
       END

       SELECT ColumnName AS [TABLA/COLUMNA] , ColumnValue AS [VALOR] FROM #Results
       DROP TABLE #Results
     
SET NOCOUNT OFF'

  IF (@Print = 1)
  BEGIN
    PRINT @Query
  END

  EXECUTE sp_executesql @Query
    ,N'@TMP NVARCHAR, @Print BIT '
    ,@TMP = @TMP
    ,@Print = @Print
END

Pueden verlo con mejor detalle descargando el código desde GitHubEl procedimiento contempla las dos variantes pudiendo buscar en toda la base de datos mandando como argumento simplemente texto a buscar Ej:

BuscarTextoEnTablas 'Datos'

O especificar un conjunto de tablas sobre las que se tiene sospecha Ej:

BuscarTextoEnTablas 'Datos IN Casa,Ciudad,Estado,Pais'

También se puede especificar si se quiere ver como quedo el query en la pestaña de mensaje de SQL Server  mandando el parámetro "1", Ej:

BuscarTextoEnTablas 'Datos IN Casa,Ciudad,Estado,Pais',1

Esto no es particularmente relevante pero tampoco creo que estorbe así que se lo deje como algo adicional. Me gustó la idea de Narayana y creo que tiene más potencial. Espero mejorarla en un futuro no muy lejano. 

Espero les resulte útil. Saludos y por adelantado, que tengan un ¡Feliz Año Nuevo!

REFERENCIAS:



martes, 24 de diciembre de 2013

SQL SERVER: GENERACIÓN DE INSERTS DE RESPALDO CONDICIONADOS.

GENERANDO INSERTS DE RESPALDO.

Mucho tiempo sin publicar, pero en fin, a lo que nos atañe. Para no explayarme sobre el contexto, lo que presento a continuación es un query para generar INSERTS de respaldo para las ocasiones en las que se requiera. SQL Server, al menos en su versión del 2008 en adelante tiene una forma de generar todos los INSERTS de una tabla, pero, al parecer no tiene una forma de filtrarlos de manera especifica para que solo se generen los INSERTS correspondientes a ciertas condiciones. La necesidad surgió debido a que en determinados escenarios requería ejecutar una depuración de determinada información, proceder a ejecutar ciertos procesos y después regresar la información a su estado original. Encontré un procedimiento almacenado escrito por Neeraj Prasad Sharma, solo requerí modificarlo para las necesidades de mi particular problema, y adecuarlo a un entorno donde no tengo permitido crear procedimientos almacenados ni tampoco debo usar respaldos en tablas temporales (algo que hubiera simplificado bastante la tarea). Realice dos versiones que tiene básicamente la misma función , ambas generan los INSERTS solo que una solo funciona posicionándose en la base de datos y la otra funciona sin problemas con varias bases de datos que estén en el mismo servidor... esto parece redundante, pero a veces es un mal necesario... y como sea fue interesante adaptarlo. Los querys se ejecutan como tal, pero, no creo que tengan ningún problema en adaptarlos a procedimientos almacenados si así lo requieren.

A modo de resumen y por cuestiones que detallare mas delante resumiré o me referiré al query como <GENERA INSERTS>. El query es el siguiente:

/*
Agradecimientos a Neeraj Prasad Sharma. Me base en gran parte en un store procedure ya elaborado por el, solo
lo adecue a las necesidades de mi escenario particular.

El cambio de nombre a las variables fue hecho para con el unico proposito de facilitar comprension. El
query original lo tome en su momento del siguiente link:
http://stackoverflow.com/questions/5065357/how-to-generate-an-insert-script-for-an-existing-sql-server-table-that-includes

Se puede encontrar tambien en :
http://www.sqlservercentral.com/scripts/scripting/94260/
*/
SET NOCOUNT ON

--VARIABLES-------------------------------------------------------
DECLARE @Esquema CHAR(3) = 'dbo'
DECLARE @Tabla VARCHAR(100) = 'DimAccount'
DECLARE @Where VARCHAR(MAX) = NULL
/*NOTAS WHERE
-En caso de ser un campo char, varchar o similares   -->   [' AND <campo>=''''<valor>'''' ']
-En caso de ser un campo int, smallint o similares   -->   [' AND <campo>=<valor>             ]
-En caso de ser un campo bit (donde <valor> = 0 | 1)-->    [' AND <campo>=<valor>             ]
*/
--VARIABLES DE ENTORNO--------------------------------------------
DECLARE @Query VARCHAR(MAX) = ''
DECLARE @Columnas TABLE (
  [N] SMALLINT
  ,Columna VARCHAR(Max)
  )
DECLARE @NoColumnas SMALLINT = 0
DECLARE @Ciclos SMALLINT = 1
DECLARE @InsertInto VARCHAR(MAX) = ''

IF (@Where IS NULL)
BEGIN
  SET @Where = ' '
END

INSERT INTO @Columnas
SELECT ORDINAL_POSITION
  ,COLUMN_NAME
FROM Information_schema.columns ISC
WHERE TABLE_SCHEMA = @Esquema
  AND TABLE_NAME = @Tabla
  AND COLUMN_NAME NOT IN (
    'SyncDestination'
    ,'PendingSyncDestination'
    ,'SkuID'
    ,'SaleCreditedto'
    )

SELECT @NoColumnas = MAX([N])
FROM @Columnas

WHILE (@Ciclos <= @NoColumnas)
BEGIN
  SELECT @InsertInto = @InsertInto + '[' + Columna + '],'
  FROM @Columnas
  WHERE [N] = @Ciclos

  SELECT @Query = @Query + ' +CASE WHEN [' + Columna + '] IS NULL THEN ''Null'' ELSE ''''''''+                             
                        REPLACE(CONVERT(VARCHAR(MAX),RTRIM([' + Columna + '])) ,'''''''',''''  )                             
                        +'''''''' END+' + ''','''
  FROM @Columnas
  WHERE [N] = @Ciclos

  SET @Ciclos = @Ciclos + 1
END

SELECT @Query = LEFT(@Query, LEN(@Query) - 4)

SELECT @InsertInto = SUBSTRING(@InsertInto, 0, LEN(@InsertInto))

SELECT @Query = ' SELECT  ' + '''INSERT INTO ' + @Esquema + '.' + @Tabla + '(' + @InsertInto + ')' + ' VALUES ( ' + '''' + '+' + @Query + '+' + ''')'' AS [BKINSERTS]' + ' FROM  ' + @Esquema + '.' + @Tabla + '(NOLOCK) ' + ' WHERE 1=1 ' + @Where

EXEC (@Query)

SET NOCOUNT OFF

Y su versión dinámica ligeramente adaptada es  <GENERA INSERTS DINAMICO>:

/*
Agradecimientos a Neeraj Prasad Sharma. Me base en gran parte en un store procedure ya elaborado por el, solo
lo adecue a las necesidades de mi escenario particular.

El cambio de nombre a las variables fue hecho para con el unico proposito de facilitar comprension. El
query original lo tome en su momento del siguiente link:
http://stackoverflow.com/questions/5065357/how-to-generate-an-insert-script-for-an-existing-sql-server-table-that-includes

Se puede encontrar tambien en :
http://www.sqlservercentral.com/scripts/scripting/94260/
*/
SET NOCOUNT ON

--VARIABLES-------------------------------------------------------
DECLARE @BaseDeDatos VARCHAR(50) = 'AdventureWorksDW2008R2'
DECLARE @Esquema CHAR(3) = 'dbo'
DECLARE @Tabla VARCHAR(100) = 'DimAccount'
DECLARE @Where VARCHAR(MAX) = ' AND AccountCodeAlternateKey>1100 '
/*NOTAS PARA WHERE
-En caso de ser un campo char, varchar o similares   -->   [' AND <campo>=''''<valor>'''' ']
-En caso de ser un campo int, smallint o similares   -->   [' AND <campo>=<valor>             ]
-En caso de ser un campo bit (donde <valor> = 0 | 1)-->    [' AND <campo>=<valor>             ]
*/
--VARIABLES DE ENTORNO--------------------------------------------
DECLARE @Query VARCHAR(MAX) = ''
DECLARE @BDActual VARCHAR(75) = (
    SELECT DB_NAME()
    )

IF (@Where IS NULL)
BEGIN
  SET @Where = ' '
END

SET @Query = '
DECLARE @Columnas  TABLE ([N] SMALLINT , Columna VARCHAR(Max) ) 
DECLARE @NoColumnas SMALLINT=0                             
DECLARE @Ciclos SMALLINT=1
DECLARE @InsertInto VARCHAR(MAX)='''' 
DECLARE @InnerQuery VARCHAR(MAX)=''''

USE ' + @BaseDeDatos + '
INSERT INTO @Columnas
SELECT
ORDINAL_POSITION,
COLUMN_NAME
FROM Information_schema.columns ISC
WHERE TABLE_SCHEMA=''' + @Esquema + ''' AND TABLE_NAME=''' + @Tabla + ''' AND TABLE_CATALOG=''' + @BaseDeDatos + ''' AND COLUMN_NAME NOT IN (''SyncDestination'',''PendingSyncDestination'',''SkuID'',''SaleCreditedto'')
ORDER BY ISC.ORDINAL_POSITION
USE ' + @BDActual + 
  '    

SELECT @NoColumnas= MAX([N]) FROM  @Columnas 

WHILE (@Ciclos<=@NoColumnas )                             
      BEGIN                              
            SELECT @InsertInto= @InsertInto+''[''+Columna+''],''           
            FROM @Columnas                              
            WHERE [N]=@Ciclos                         

            SELECT      @InnerQuery=@InnerQuery+'' +CASE WHEN [''+Columna+''] IS NULL THEN ''''Null'''' ELSE ''''''''''''''''+                             
                        REPLACE(CONVERT(VARCHAR(MAX),RTRIM([''+Columna+''])) ,'''''''''''''''',''''''''  )                             
                        +'''''''''''''''' END+' + '''+'''''',''''''' + 
  '                              
            FROM @Columnas                             
            WHERE [N]=@Ciclos                             

            SET @Ciclos=@Ciclos+1                             
      END    
                            
SELECT @InnerQuery=LEFT(@InnerQuery,LEN(@InnerQuery)-4)             
SELECT @InsertInto= SUBSTRING(@InsertInto,0, LEN(@InsertInto))   
SELECT @InnerQuery='' SELECT  ''+''''''INSERT INTO ' + @BaseDeDatos + '.' + @Esquema + '.' + @Tabla + '''+''(''+@InsertInto+'')''
                  +'' VALUES ( ''+'''''''' + ''+''+@InnerQuery+''+''+ '''''')'''' AS [BKINSERTS]''
                  +'' FROM  ' + @BaseDeDatos + '.' + @Esquema + '.' + @Tabla + '(NOLOCK) ''
                  +'' WHERE 1=1 ' + @Where + '''' + CHAR(13) + + ' EXEC (@InnerQuery) '

EXEC (@Query)

SET NOCOUNT OFF

En su versión fuente de Neeraj Prasad Sharma los parámetros de tabla y el condicionante no están divididos y se pasa todo como una consulta general omitiendo el "SELECT * FROM" -por decirlo así -, es decir, a modo de ejemplo un parámetro valido seria: 

'dbo.DimAccount WHERE AccountCodeAlternateKey>1100'

Ahora, ¿Por que no lo deje así?. Bueno, por un lado fue una cuestión de orden y por otro, una de control al separar las secciones de las consultas en Base de Datos, Esquema, Tabla y Where, me resulta mas sencillo por ejemplo, generar tablas con esos datos y someter el procesos en un ciclo WHILE para generar varios INSERTS de manera automática si en dado caso así lo requiero. Por ejemplo: 

DECLARE @INSERTSTABLE TABLE
([N] SMALLINT IDENTITY, 
 BD VARCHAR(50,
 Esquema CHAR(3),
 Tabla VARCHAR(100),
 [Where] VARCHAR(MAX))

INSERT INTO @INSERTSTABLE VALUES('AdventureWorksDW2008R2','dbo','DimAccount',' AND AccountCodeAlternateKey>1100 ')
INSERT INTO @INSERTSTABLE VALUES('AdventureWorksDW2008R2','dbo','DatabaseLog',' AND object=''''DimProduct'''' ')
INSERT INTO @INSERTSTABLE VALUES('Lab','dbo','Test',NULL)
INSERT INTO @INSERTSTABLE VALUES('Lab','dbo','TrashData',' AND Value=''''TRASH'''' ')

WHILE (@Ciclos<=(SELECT MAX([N]) FROM @INSERTSTABLE))                             
       BEGIN 
             <PASAR PARAMETROS TOMADOS DE LA POSICION DE LA TABLA @INSERTSTABLE>                            
             <GENERA INSERTS> o <GENERA INSERTS DINAMICO
             SET @Ciclos=@Ciclos+1                             
       END     

Esto claro en caso de que no puedas crear un procedimiento almacenado como tal... en cuyo caso se vería mas elegante. Aquí solo hay que tener cuidado en lo que se pasa como valor para  WHERE en donde en caso de que se filtren datos usando un campo CHAR, VARCHAR o similares debe de cumplir con cierto formato. Si, por otro lado se te permite trabajar sin problemas con tablas temporales, quizá estos query resulten innecesarios y muy rebuscados. Creo que aun puede pulirse un par de detalles que espero ir trabajando mientras le vaya dando uso. Entre tanto, espero que a alguien le resulte útil. Comentarios sobre mejoras o detalles se reciben con gusto. 

Saludos.

REFERENCIAS:


martes, 6 de agosto de 2013

SQL SERVER: CONSULTAS ÚTILES

CONSULTAS PARA AUDITORIA, OPTIMIZACIÓN, SIMPLE CURIOSIDAD O LO QUE SE TE OCURRA...

Les presento un listado de consultas que pueden resultar útiles (lo han sido para mí en distintas ocasiones). Algunas de ellas ofrecen salidas rápidas a problemas comunes, otras nos ayudaran a tener un panorama general de la base de datos con la que estamos tratando, ya sea por el simple hecho de querer tener un contexto, o que lo requiramos para propósitos de optimización general. La mayoría de ellas ya son conocidas, en su respectivo caso cito la fuente de donde las tome, por si gustan darse una vuelta por el site y ver que les puede servir. Con algunas me tome la molestia de parametrizarlas a fin de que puedan resultar más flexibles o por si a alguno le da por convertirlas en store procedure.

BUSCAR REGISTROS DUPLICADOS

USE <DB>
SELECT <Campo>, COUNT(*) AS Registros
FROM <TABLA>(NOLOCK)
GROUP BY <Campo>
HAVING COUNT(*) > 1


BUSCAR REFERENCIAS DE TEXTO EN TRIGGERS, FUNCIONES Y STORE PROCEDURES

Este es por lo general de mi particular uso frecuente al momento de  analizar alguna inconsistencia de datos, me dan un panorama de que store procedure, triggers o functions. pueden estar afectando que tablas, buscando, por ejemplo un "UPDATE <Tabla>" dentro de los mismos.

SET NOCOUNT ON
DECLARE @Texto VARCHAR(50)=NULL -->Texto a buscar. Normalmente lo uso para buscar                                            -->referencias a tablas o otros SP

       SELECT
       ROUTINE_TYPE AS TIPO,
       ROUTINE_SCHEMA AS ESQUEMA,
       ROUTINE_NAME AS NOMBRE,
       ROUTINE_DEFINITION AS DETALLE
       FROM INFORMATION_SCHEMA.ROUTINES(NOLOCK)
    WHERE ROUTINE_DEFINITION LIKE '%'+@Texto+'%'
SET NOCOUNT OFF


REVISAR CAMPOS EN ESTRUCTURAS

-http://www.mistrucos.net/truco-sql-server-buscar-un-campo-una-base-datos-501.htm
DECLARE @Columna NVARCHAR(25)= -->Columna a buscar

SELECT TABLE_NAME,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Columna+'%'


LISTA DE PROCEDIMIENTOS ALMACENADOS MODIFICADOS EN LOS ÚLTIMOS
“N” 
DÍAS

--http://www.sqlservercentral.com/scripts/Administration/98329/
DECLARE @DIAS INT=500 -->Numero de dias de antiguedad

SET NOCOUNT ON
       SELECT *
       FROM sys.objects (NOLOCK)
       WHERE TYPE = 'P'
       AND DATEDIFF(D,modify_date, GETDATE()) < @DIAS
SET NOCOUNT OFF

SACAR ESQUEMA BASICO DE TABLA (SIMILAR A ALT+F1)

Esto puede resultar redundante, es decir, ¿Para que quiero una consulta si ya lo puedo traer con ALT+F1?, pero, se le puede dar usos interesantes para revisiones automáticas usando Jobs y otras herramientas (:P), les dejo de tarea el uso creativo que se le puede dar a este tipo de consultas.

DECLARE @table VARCHAR(50)=NULL --> Tabla. 

SELECT  
c.table_name As Tabla,  
c.column_name As Columna,  
c.data_type As Tipo,  
c.character_maximum_length As Longitud, 
  COALESCE ( 
  ( SELECT  
    CASE cu.column_name 
      WHEN NULL THEN
      ELSE
    END 
  FROM information_schema.constraint_column_usage cu 
  INNER JOIN information_schema.table_constraints ct 
  ON ct.constraint_name = cu.constraint_name 
  WHERE  
  ct.constraint_type = 'PRIMARY KEY'  
  AND ct.table_name = c.table_name 
  AND cu.column_name = c.column_name  
  ),0) AS PK
FROM information_schema.columns c 
INNER JOIN information_schema.tables t 
ON c.table_name = t.table_name 
WHERE @table = t.table_name AND  
  (t.table_type = 'BASE TABLE' AND NOT  
  (t.table_name = 'dtproperties') AND NOT  
  (t.table_name = 'sysdiagrams')) 
ORDER BY c.table_name, c.ordinal_position


NUMERO DE REGISTROS POR TABLA

Este puede resultar particularmente útil para saber que campo vamos a usar como indice, en caso de que la tabla no lo tenga, o, en su defecto, si vamos a redefinirlo.

SET NOCOUNT ON
       SELECT sysobjects.Name as TABLA, sysindexes.Rows as REGISTROS
       FROM sysobjects(NOLOCK)
       INNER JOIN sysindexes(NOLOCK) ON sysobjects.id = sysindexes.id
       WHERE type = 'U'
       AND sysindexes.IndId < 2
       ORDER BY sysindexes.Rows desc
SET NOCOUNT OFF

REGRESAR TABLA EN FORMATO XML

 SELECT * FROM <Tabla> FOR XML AUTO

IDENTIFICA LOS QUERYS QUE ESTAN GENERANDO ALTOS INDICES DE I/O

/*
Troubleshooting I/O bottleneck.
Applies to: SQL Server 2005/2008.
www.SqlCoffee.com
http://www.sqlcoffee.com/Tuning01.htm
*/
SET NOCOUNT ON
       USE MASTER
       SELECT TOP 20 (qs.total_logical_reads/qs.execution_count) AS LogicalReads,
       (qs.total_logical_writes/qs.execution_count) AS LogicalWrites,
       (qs.total_physical_reads/qs.execution_count) AS PhysicalReads,
       qs.Execution_count, qs.sql_handle, qs.plan_handle, qt.Text
       FROM sys.dm_exec_query_stats qs  cross apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
       INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle=cp.plan_handle
       ORDER BY (qs.total_logical_reads + qs.total_logical_writes) DESC
SET NOCOUNT OFF

--Use the following query to examine the query plan / Use el siguiente query para examinar el plan
SELECT * FROM sys.dm_exec_query_plan (<plan_handle>)

RETORNAR TODAS LAS TABLAS Y SU INFORMACION

--http://www.sqlservercentral.com/scripts/tables/97194/
SET NOCOUNT ON
SELECT
sys.tables.name AS [TABLE],
sys.tables.create_date AS CREATE_DATE,
sys.tables.modify_date AS MODIFY_DATE,
CASE
WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id)
ELSE sys.database_principals.name
END AS OWNER,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,
sys.tables.max_column_id_used AS COUNT_COLUMNS,
sys.partitions.rows AS COUNT_ROWS,
SUM(ISNULL(CASE
           WHEN sys.allocation_units.type <> 1 THEN USED_PAGES
           WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES
           ELSE 0 END, 0)) *
               (SELECT low / 1024 AS VALUE
                FROM master.dbo.spt_values
                WHERE (number = 1) AND (type = N'E')) AS SIZE_DATA_KB,
SUM(ISNULL(sys.allocation_units.used_pages - 
           CASE
           WHEN sys.allocation_units.type <> 1 THEN USED_PAGES
           WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES 
           ELSE 0 END, 0))*
                (SELECT low / 1024 AS VALUE
                 FROM master.dbo.spt_values AS spt_values_2
                 WHERE (number = 1) AND (type = N'E')) AS SIZE_INDEX_KB
FROM sys.allocation_units(NOLOCK)
INNER JOIN sys.partitions(NOLOCK
ON sys.allocation_units.container_id = sys.partitions.partition_id
INNER JOIN (SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, 
COUNT(*) AS COUNT_TYPE
FROM sys.indexes(NOLOCK)  AS indexes_1 GROUP BY object_id, type, index_id                    ORDER BY object_id) AS INDEXES 
ON sys.partitions.object_id = INDEXES.object_id 
AND sys.partitions.index_id = INDEXES.index_id 
RIGHT OUTER JOIN sys.database_principals 
RIGHT OUTER JOIN sys.tables 
ON sys.database_principals.principal_id = sys.tables.principal_id 
ON INDEXES.object_id = sys.tables.object_id 
GROUP BY sys.tables.name, sys.tables.create_date, sys.tables.modify_date,
CASE WHEN sys.database_principals.name IS NULL 
THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END,
sys.tables.max_column_id_used, sys.partitions.rows
ORDER BY COUNT_ROWS DESC
SET NOCOUNT OFF

BUSCAR COLUMNAS IDENTIDAD

--http://www.areatic.net/2012/11/sql-server-como-saber-si-hay-columnas.html
SET NOCOUNT ON
       SELECT SCHEMA_NAME(OBJECTPROPERTY(OBJECT_ID,'SchemaId')) AS SchemaName,
                OBJECT_NAME(OBJECT_ID) AS TableName,
                name AS ColumnName
       FROM  SYS.COLUMNS(NOLOCK)
       WHERE is_identity = 1
       ORDER BY SchemaName, TableName, ColumnName
SET NOCOUNT OFF

MONITOREO DE EL ESTADO DE LOS JOBS FALLADOS EN LA ULTIMA EJECUCION

--http://sqldata.blogspot.mx/2009/01/algunos-scripts-para-monitorear-sql.html
SET NOCOUNT ON
       SELECT name AS [JOB's FALLIDOS]
       FROM msdb.dbo.sysjobs A(NOLOCK), msdb.dbo.sysjobservers B(NOLOCK)
       WHERE A.job_id = B.job_id AND B.last_run_outcome = 0
SET NOCOUNT OFF
                          
ESPACIO EN CADA HD PARA INSTANCIAS DE SQL

EXEC master..xp_fixeddrives

VISUALIZAR LISTADO DE JOBS DESHABILITADOS

SET NOCOUNT ON
       SELECT name AS [JOB´s DESHABILITADOS]
       FROM msdb.dbo.sysjobs(NOLOCK)    
WHERE enabled = 0 ORDER BY name
SET NOCOUNT OFF

VISUALIZAR LISTADO DE JOBS EN EJECUCION ACTUALMENTE

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

REVISAR FECHA DE ÚLTIMO BACKUP EFECTUADO A LAS BD

SET NOCOUNT ON
       SELECT  B.name AS Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
       MAX(Backup_finish_date)))),
       'NEVER') as DaysSinceLastBackup,
       ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER')
       AS LastBackupDate
       FROM master.dbo.sysdatabases B(NOLOCK)
       LEFT OUTER JOIN msdb.dbo.backupset A(NOLOCK) ON A.database_name = B.name AND A.type = 'D'
       GROUP BY B.Name
       ORDER BY B.name
SET NOCOUNT OFF

REVISAR LAS ÚLTIMAS ENTRADAS DEL ARCHIVO LOG (NO DEL LOG TRANSACCTION)

 SET NOCOUNT ON
       CREATE TABLE #Errors (LogDate datetime, ProcessInfo varchar(100), [Text] text)
       INSERT #Errors EXEC xp_readerrorlog
       SELECT LogDate,ProcessInfo,[Text] --SELECT * FROM #Errors
       FROM #Errors
       WHERE [Text]
       NOT LIKE '%Log backed up%' AND [Text]
       NOT LIKE '%.TRN%' AND [Text]
       NOT LIKE '%Database backed up%' AND [Text]
       NOT LIKE '%.BAK%' AND [Text]
       NOT LIKE '%Run the RECONFIGURE%' AND
       [Text] NOT LIKE '%Copyright (c)%'
       ORDER BY LogDate asc

       DROP TABLE #Errors
SET NOCOUNT OFF

RECOMPILAR SP
/*
http://www.sqlcoffee.com/Tuning03.htm
System Stored Procedure sp_recompile
By: Alberto Morillo - www.sqlcoffee.com

Usar cuando se añadan nuevos indices a tablas o se modifique la estructura de una tabla.
El ejemplo siguiente es usado en la tabla  Employees de la Base de datos AdventureWorks.
*/
USE AdventureWorks;
GO
EXEC sp_recompile N'HumanResources.Employee';
GO

LISTAR BASES DE DATOS

EXEC sp_helpdb
SELECT * FROM master.dbo.sysdatabases


REFERENCIAS:

http://www.sqlcoffee.com/
http://sqldata.blogspot.mx
http://www.areatic.net
http://www.mistrucos.net
http://www.sqlservercentral.com