Searching All Tables in a SQL Server Database for a value.

One of my IT guys was trying to write a SQL query to search for a keyword in every table and column of a particular database. There are tons of solutions out there on how to do this and from looking at what he had done so far, I decided to see how quickly I could figure out one. Ended up being about 15 minutes.

CAUTION: Probably not the fastest or most optimized solution, but fairly simple:


USE My_Database;

-- Store results in a local temp table so that. I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
CREATE TABLE #tmp (
tbl nvarchar(max),
col nvarchar(max),
val nvarchar(max)
);

DECLARE @tbl nvarchar(max);
DECLARE @col nvarchar(max);
DECLARE @q nvarchar(max);
DECLARE @search nvarchar(max) = 'my search key';

-- Create a cursor on all columns in the database
declare c cursor for
SELECT tbls.TABLE_NAME, cols.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
OPEN c
FETCH NEXT FROM c INTO @tbl, @col
WHILE @@FETCH_STATUS = 0
BEGIN
-- Look for the search key in current table column and if found add it to the results.
SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
EXEC SP_EXECUTESQL @q
FETCH NEXT FROM C INTO @tbl, @col
END
CLOSE c
DEALLOCATE c

-- Get results
SELECT tbl, col, val FROM #tmp

-- Remove local temp table.
DROP TABLE #tmp

Adding Column Messes Up Existing SQL View

So I needed to add a new form field to an existing application, which usually requires a new column to a SQL table. Cool, no biggie. Everything is fine and dandy until I go to SharePoint to view the external lists connected to the database, I kept getting errors about string to datetime conversion. WTF…

These external lists were reading from SQL Views, so I took a look at the content coming from the views, and about halfway through the columns everything was shifted right by one. This happened at exactly where I inserted the new column (I didn’t append it to the table). The issue is that when I created the view, I used a select * instead of implicitly stating the columns. When you do this, I guess SQL Server expands * to all existing columns to make “select a, b, c, d from table” and saves THAT as the View query. So when the “select” from view was being called from SharePoint, it was referencing the old “c” column position but now that was different. (NULL for all existing rows)

This should help you to visualize:

Before:
1) Created view with select *
2) SQL Server expanded to select a, b, c, d and saved as view.
3) Now by selecting all from view, it’s using: select a, b, c, d from myView

a | b | c | d
—————
1 | 2 | 3 | 4

After:
1) Added column newCol after column b
2) View now still calling using: select a,b, c, d from myView, but now newCol was being used for c, and then c was used:

a | b | c | d
——————
1 | 2 | NULL | 3

AH d is using c column value!! So the rest are now off by one.

To fix, just ran ALTER on the view

But lesson learned, DON’T create views with SELECT *

Converting Datetime to VARCHAR of a Different Time Format

I needed to change the following datetime values Jun 20 1984 12:00 AM to a varchar(10) value of 1984/6/20. The following SQL Server code accomplishes that:


update mydbtable
set birthday = (SELECT CONVERT(VARCHAR(10), CONVERT(datetime, birthday), 120))

Note: I changed the column from datetime to varchar(50) before doing this.

‘Subquery returned more than 1 value’ error on UPDATE when Trigger attached

If you try to do a mass update to a table that has an update trigger attached to it, you will get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

All you need to do to fix this is to disable your trigger, run the query, enable the trigger. This can be done like this:


ALTER TABLE my_table DISABLE TRIGGER my_table_trigger
update my_table set col_name = new_value
ALTER TABLE my_table ENABLE TRIGGER my_table_trigger