recuperar Metadatos con cunsulta

[flaviovich]

Esta seguro que no es la mejor solucion, pero funciona:

Código sql:

CREATE TABLE #temp(tabla VARCHAR(255))
DECLARE @tabla VARCHAR(255), @columna VARCHAR(255)
 
DECLARE Tablas CURSOR FOR
    SELECT T.TABLE_NAME, C.column_name
    FROM information_schema.TABLES T
    INNER JOIN information_schema.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
    WHERE T.TABLE_NAME NOT LIKE 'sys%' 
    AND T.TABLE_NAME <> 'dtproperties'
    AND T.table_schema <> 'INFORMATION_SCHEMA'
    AND C.column_name LIKE '%mes%' 
    AND T.TABLE_NAME NOT LIKE 'VX%'
    AND T.TABLE_NAME NOT LIKE 'XX%'
    AND T.TABLE_NAME NOT LIKE 'zz%'
    ORDER BY T.TABLE_NAME, C.TABLE_NAME
FOR READ ONLY
 
OPEN Tablas
FETCH NEXT FROM Tablas INTO @tabla, @columna
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
    EXEC('
        IF EXISTS(SELECT 1 FROM '+@tabla+' WHERE '+@columna+' = 12)
            INSERT INTO #temp VALUES('''+@tabla+''')
        ')
    FETCH NEXT FROM Tablas INTO @tabla, @columna
END
 
CLOSE Tablas
DEALLOCATE Tablas
 
SELECT * FROM #temp