Search Microsoft SQL Server Database for Stored Data
Have you ever had to decipher a database and identify which column has the data you need?
I recently had to work with extracting data from a SQL database to use in Microsoft Access and Power BI, unfortunately the naming structure of the database was not very intuitive.
The only guide I had was a report showing an example of the data that needed to be extracted, with labels that had no reference to the column names. This could have meant hours of work searching the database and reviewing each table, this particular database had 288 tables.
I have used code in the past to search for a column in tables, but in this case that would not help me.
Luckily, I came across a very interesting article https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008, this was perfect for my needs. I simply needed to set the variable with the text I was looking for and run the code. In a matter of seconds I was provided with a list of the tables and columns where the text appeared. Before long I had a list of the tables and columns that I needed to include in my project.
Please note this solution applies to SQL Server tables only and is run in SQL Server Management Studio. You also need to have s SQL Server instance that supports table variables.
USE DATABASE_NAME DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT' DECLARE @Results TABLE (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', 'int', 'decimal') 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
I hope this helps you too!