Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | 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 |
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.
1 2 3 | USE [TestDB] GO [dbo].[Search_Text_in_Whole_Database] 'Nurullah' |
