MS Sqlde aranan bir ifadeyi bütün tablo ve sahalarda arayan stored procedure

11.10.2014 2883 defa okundu

Bazı durumlarda özellikle onlarca veya yüzlerce tablo ve binlerce saha bulunan bir veri tabanında bir verinin hangi tablo veya tablolarda hangi saha veya sahalarda olduğu bulmak son derece güçtür. Bu gibi sorunun en iyi çözümü aşağıdaki stored procedureyi kullanmaktır. Çok büyük veritabanlarında yavaş sonuç getirse de tek tek tablolarda bir veri bulmaktan daha efektif bir yöntem olduğunu düşünüyorum. Stored procedured aşağıdaki gibi oluşturulur.

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