Weblogs Código

RecorteX

73 usuarios y 244 recortes de código
Usuario

Contraseña
Crear cuenta
asp csharp css fechas Flash html imagenes java javascript linux mysql oracle php sql SQLServer url vbnet web xhtml xml
Top Usuarios
fjmp76 (43)
albin (31)
josuebasurto (31)
juanjo (30)
pedrocaro (9)
stanz (8)
AndiXTRM (7)
Joserra (7)
miiglesi (6)
aplyca (5)
Top Etiquetas
php (38)
vbnet (28)
csharp (26)
javascript (26)
html (25)
java (24)
sql (18)
xml (13)
mysql (13)
css (10)
Flash (7)
imagenes (7)
xhtml (7)
linux (6)
asp (6)
fechas (6)
web (5)
SQLServer (5)
oracle (5)
url (5)

Hacer busquedas de texto en toda la base de datos

Procedimiento para hacer busquedas en toda la base de datos, para ejecutarlo:
EXEC SearchAllTables ‘Palabra o cadena a buscar’.

Procedimiento:

 
 
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
 
-- 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
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
 
 
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
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
)
 
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 ' + @SearchStr2
)
END
END 
END
 
SELECT ColumnName, ColumnValue FROM #Results
END
 
 
GO
 
Identifícate para dejar un comentario.
Proyecto: Juanjo Navarro, 2006 - Diseño: Albin