I needed to find out which columns within all tables in the database, contained a specific string of text. I therefore wrote the script below to do exactly that
CREATE TABLE TempTableColumnResults
( TableName VARCHAR(100),
ColumnName VARCHAR(100)
)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(200)
DECLARE @SQL VARCHAR(1000)
DECLARE @SearchText VARCHAR(100)
SET @SearchText = 'Text to find'
DECLARE TableColumns CURSOR FAST_FORWARD
FOR SELECT
O.name AS TableName,
C.name AS ColumnName
FROM SYSCOLUMNS C
INNER JOIN SYSOBJECTS O
ON C.ID = O.ID
WHERE O.xtype IN ('U')
AND C.xtype NOT IN (34,241,98) --Exclude image, sql_variant, xml data types
ORDER BY O.name, C.ColOrder
OPEN TableColumns
FETCH NEXT FROM TableColumns
INTO @TableName, @ColumnName
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT INTO TempTableColumnResults (TableName, ColumnName) '
SET @SQL = @SQL + 'SELECT ' + CHAR(39) + @TableName + CHAR(39) + ', '
SET @SQL = @SQL + CHAR(39) + @ColumnName + CHAR(39) + ' '
SET @SQL = @SQL + 'FROM ' + QUOTENAME(@TableName) + ' '
SET @SQL = @SQL + 'WHERE ' + QUOTENAME(@ColumnName) + ' LIKE '
SET @SQL = @SQL + CHAR(39) + '%' + @SearchText + '%' + CHAR(39) + ' '
EXEC (@SQL)
FETCH NEXT FROM TableColumns
INTO @TableName, @ColumnName
END
CLOSE TableColumns
DEALLOCATE TableColumns
SELECT DISTINCT TableName, ColumnName FROM TempTableColumnResults
DROP TABLE TempTableColumnResults
Labels:
SQL
( TableName VARCHAR(100),
ColumnName VARCHAR(100)
)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(200)
DECLARE @SQL VARCHAR(1000)
DECLARE @SearchText VARCHAR(100)
SET @SearchText = 'Text to find'
DECLARE TableColumns CURSOR FAST_FORWARD
FOR SELECT
O.name AS TableName,
C.name AS ColumnName
FROM SYSCOLUMNS C
INNER JOIN SYSOBJECTS O
ON C.ID = O.ID
WHERE O.xtype IN ('U')
AND C.xtype NOT IN (34,241,98) --Exclude image, sql_variant, xml data types
ORDER BY O.name, C.ColOrder
OPEN TableColumns
FETCH NEXT FROM TableColumns
INTO @TableName, @ColumnName
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT INTO TempTableColumnResults (TableName, ColumnName) '
SET @SQL = @SQL + 'SELECT ' + CHAR(39) + @TableName + CHAR(39) + ', '
SET @SQL = @SQL + CHAR(39) + @ColumnName + CHAR(39) + ' '
SET @SQL = @SQL + 'FROM ' + QUOTENAME(@TableName) + ' '
SET @SQL = @SQL + 'WHERE ' + QUOTENAME(@ColumnName) + ' LIKE '
SET @SQL = @SQL + CHAR(39) + '%' + @SearchText + '%' + CHAR(39) + ' '
EXEC (@SQL)
FETCH NEXT FROM TableColumns
INTO @TableName, @ColumnName
END
CLOSE TableColumns
DEALLOCATE TableColumns
SELECT DISTINCT TableName, ColumnName FROM TempTableColumnResults
DROP TABLE TempTableColumnResults
Responses
0 Respones to "Search all table columns for specific Text"
Post a Comment