How To Find a Column or a Word in Whole Database in SQL Server

dbtut

6 years ago

Find a Column Name in Whole Database

Sometimes you may want to search for a column name in whole database. Instead of looking all the columns of all the tables one by one, you can use one of the following scripts.

For example, suppose that we search a column named “phone” in whole database.

SELECT table_name AS [Table Name], Column_name AS [Column Name] 
FROM Information_Schema.Columns where Column_Name like '%Phone%'

SELECT DISTINCT object_name(object_id) AS [TableName], name AS [Column Name] 
FROM sys.columns WHERE name like '%Phone%'

SELECT table_name AS [Table Name], Column_name AS [Column Name]

FROM Information_Schema.Columns where Column_Name like '%Phone%'

SELECT DISTINCT object_name(object_id) AS [TableName], name AS [Column Name]

FROM sys.columns WHERE name like '%Phone%'

Find a Word in Whole Database

Or, you may want to search for a text in the entire database. You can use the following stored procedure for this.

USE [your_db_name]
GO
CREATE PROC [dbo].[Search_Text_in_Whole_Database]
(
@Search nvarchar(100)
)
AS
BEGIN
CREATE TABLE Result (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @Search2 nvarchar(110)
SET @TableName =''
SET @Search2 = QUOTENAME('%' + @Search + '%','''')

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 Result EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @Search2 )
END
END
END
SELECT ColumnName AS [Table and Column Name], ColumnValue AS [Column Value] FROM Result
END

GO

USE [your_db_name]

CREATE PROC [dbo].[Search_Text_in_Whole_Database]

(

@Search nvarchar(100)

)

BEGIN

CREATE TABLE Result (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @Search2 nvarchar(110)

SET @TableName =''

SET @Search2 = QUOTENAME('%' + @Search + '%','''')

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 Result EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @Search2 )

END

SELECT ColumnName AS [Table and Column Name], ColumnValue AS [Column Value] FROM Result

END

After you create the Stored procedure, you can run it as follows. In large databases, you must be careful when running this query. Because all the data in the database will be scanned.

Lest execute stored procedure on TestDB for searching “Nurullah” in the whole database.

The query will return all Nurullah values in the database as below.

USE [TestDB]
GO
[dbo].[Search_Text_in_Whole_Database] 'Nurullah'

USE [TestDB]

[dbo].[Search_Text_in_Whole_Database] 'Nurullah'