MS SQL 2008 R2 search word in all tables

Share request from your repository. It is necessary to find in what table is stored the text url ("http:// ...").
October 8th 19 at 03:28
1 answer
October 8th 19 at 03:30
Once upon a time used to MSSQL2000, in 2008, there is no way to verify, try it for yourself.

CREATE PROC SearchAllTables (@SearchStr nvarchar(100))
AS
BEGIN
 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


Example usage:
EXEC SearchAllTables 'Your string'
GO
2008 is not rolling, the query gives nothing - Freida.Wintheiser commented on October 8th 19 at 03:33
I have soared. If the short was from MSSQL database 1C, which features a leg break. The task was to pull data from a database and throw them on the website. MS SQL SERVER 2008 r2. This function found the data you need, and where they lie. A load off my mind. - darrel.Hartmann commented on October 8th 19 at 03:36
MS SQL SERVER 2008 r2. Also took off. Thanks for the solution - Dakota25 commented on October 8th 19 at 03:39

Find more questions by tags SQL