consulta sql para buscar una string en todas las tablas ejemplo de código

Ejemplo 1: buscar valor en todas las tablas sql

DECLARE@SearchStr nvarchar(100)SET@SearchStr='## YOUR STRING HERE ##'-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.-- Purpose: To search all columns of all tables for a given search string-- Written by: Narayana Vyas Kondreddi-- Site: http://vyaskn.tripod.com-- Updated and tested by Tim Gaunt-- http://www.thesitedoctor.co.uk-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010-- Date modified: 03rd March 2011 19:00 GMTCREATETABLE#Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))SET NOCOUNT ONDECLARE@TableName nvarchar(256),@ColumnName nvarchar(128),@SearchStr2 nvarchar(110)SET@TableName=''SET@SearchStr2= QUOTENAME('%'+@SearchStr+'%','''')WHILE@TableNameISNOTNULLBEGINSET@ColumnName=''SET@TableName=(SELECTMIN(QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME))FROM     INFORMATION_SCHEMA.TABLESWHERE         TABLE_TYPE ='BASE TABLE'AND    QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)>@TableNameAND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)),'IsMSShipped')=0)WHILE(@TableNameISNOTNULL)AND(@ColumnNameISNOTNULL)BEGINSET@ColumnName=(SELECTMIN(QUOTENAME(COLUMN_NAME))FROM     INFORMATION_SCHEMA.COLUMNSWHERE         TABLE_SCHEMA    = PARSENAME(@TableName,2)AND    TABLE_NAME    = PARSENAME(@TableName,1)AND    DATA_TYPE IN('char','varchar','nchar','nvarchar','int','decimal')AND    QUOTENAME(COLUMN_NAME)>@ColumnName)IF@ColumnNameISNOTNULLBEGININSERTINTO#ResultsEXEC('SELECT '''+@TableName+'.'+@ColumnName+''', LEFT('+@ColumnName+', 3630) FROM '+@TableName+' (NOLOCK) '+' WHERE '+@ColumnName+' LIKE '+@SearchStr2)ENDENDENDSELECT ColumnName, ColumnValue FROM#ResultsDROPTABLE#Results

Ejemplo 2: buscar un valor en todas las tablas SQL

DECLARE@SearchStr nvarchar(100)SET@SearchStr='## YOUR STRING HERE ##'-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.-- Purpose: To search all columns of all tables for a given search string-- Written by: Narayana Vyas Kondreddi-- Site: http://vyaskn.tripod.com-- Updated and tested by Tim Gaunt-- http://www.thesitedoctor.co.uk-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010-- Date modified: 03rd March 2011 19:00 GMTCREATETABLE#Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))SET NOCOUNT ONDECLARE@TableName nvarchar(256),@ColumnName nvarchar(128),@SearchStr2 nvarchar(110)SET@TableName=''SET@SearchStr2= QUOTENAME('%'+@SearchStr+'%','''')WHILE@TableNameISNOTNULLBEGINSET@ColumnName=''SET@TableName=(SELECTMIN(QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME))FROM     INFORMATION_SCHEMA.TABLESWHERE         TABLE_TYPE ='BASE TABLE'AND    QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)>@TableNameAND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)),'IsMSShipped')=0)WHILE(@TableNameISNOTNULL)AND(@ColumnNameISNOTNULL)BEGINSET@ColumnName=(SELECTMIN(QUOTENAME(COLUMN_NAME))FROM     INFORMATION_SCHEMA.COLUMNSWHERE         TABLE_SCHEMA    = PARSENAME(@TableName,2)AND    TABLE_NAME    = PARSENAME(@TableName,1)AND    DATA_TYPE IN('char','varchar','nchar','nvarchar','int','decimal')AND    QUOTENAME(COLUMN_NAME)>@ColumnName)IF@ColumnNameISNOTNULLBEGININSERTINTO#ResultsEXEC('SELECT '''+@TableName+'.'+@ColumnName+''', LEFT('+@ColumnName+', 3630) FROM '+@TableName+' (NOLOCK) '+' WHERE '+@ColumnName+' LIKE '+@SearchStr2)ENDENDENDSELECT ColumnName, ColumnValue FROM#ResultsDROPTABLE#Results

Puedes apoyar nuestro trabajo fijando un comentario o valorándolo te estamos agradecidos.

---

---

Utiliza Nuestro Buscador