Sql Server数据库全文搜索脚本

eagleshi

　　


----------------------------------------------------------------------------------------
--How to find the table and column name in the db that contains a certain record value--
----------------------------------------------------------------------------------------
--DROP TABLE #searchresults
DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'PORECEIPTIAS'
CREATE TABLE #searchresults (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',  'numeric','decimal', 'double', 'money')   
AND   QUOTENAME(COLUMN_NAME) > @ColumnName   
)   
IF @ColumnName IS NOT NULL   
BEGIN   
INSERT INTO #searchresults   
EXEC   
(   
'SELECT ''' + @TableName + '.' + @ColumnName +  ''', LEFT(' + @ColumnName + ', 3630)   
FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)   
END   
END      
END   
select * from #searchresults