I'm trying to search an entire database for all returns of a specific date. I've used the code below however its not picking up the column I expected.
The table column with the data is a datetime column, and the specific value contained is 2010-09-10 13:33:11.441, my search string (based on the script from this website) is like %2010-09-10%, but the search return doesnt show the datetime column expected. It does pick up on a varchar field that has the text 2010-09-10.
I'm sure its something simple to do with searching a datetime specific field?? Any pointers please.
USE MAIN_DATABASE
DECLARE @SearchStr nvarchar(100) = '2010-09-10'
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', 'datetime', '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
Here's a version that might work for you:
DROP TABLE IF EXISTS dummy
CREATE TABLE dummy (
a varchar(30) DEFAULT '2023-03-06'
, b char(30) DEFAULT '2023-03-06'
, c nchar(30) DEFAULT '2023-03-06'
, d nvarchar(30) DEFAULT '2023-03-06'
, e nvarchar(max) DEFAULT '2023-03-06'
, f xml DEFAULT '<root a="2023-03-06"></root>'
, g text DEFAULT '2023-03-06'
, h ntext DEFAULT '2023-03-06'
, i sql_variant DEFAULT '2023-03-06'
, j datetime DEFAULT '20230306'
, k date DEFAULT '20230306'
, l smalldatetime DEFAULT '20230306'
, m datetime2 DEFAULT '20230306'
, nERROR sql_variant default 0
, o sql_variant default CAST('20230306' AS DATETIME)
, p sql_variant default CAST('20230306' AS DATETIME2)
, q sql_variant default CAST('20230306' AS DATE)
, r sql_variant default CAST('20230306' AS SMALLDATETIME)
, s sql_variant default CAST('20230306' AS DATETIMEOFFSET)
, t datetimeoffset DEFAULT '20230306'
)
INSERT INTO dummy
DEFAULT VALUES;
DECLARE @SearchStr nvarchar(100) = '%2023-03-06%'
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (tablename sysname, ColumnName sysname, ColumnValue nvarchar(MAX))
DECLARE @sql nvarchar(max), @table sysname, @col sysname, @type int
SET NOCOUNT ON
DECLARE cr_x CURSOR read_only forward_only local static FOR
SELECT so.name, sc.name, CASE WHEN ts.name IN ('char', 'nchar','varchar', 'nvarchar', 'text', 'ntext', 'xml') THEN 0 when ts.name = 'sql_variant' then 1 ELSE 2 END
FROM sys.tables so
INNER JOIN sys.columns sc
ON sc.object_id = so.object_id
INNER JOIN sys.types ts
ON ts.user_type_id = sc.user_type_id
WHERE ts.name IN ('char', 'nchar','varchar', 'nvarchar', 'text', 'ntext', 'xml', 'datetime', 'date', 'smalldatetime', 'datetime2','datetimeoffset','sql_variant')
OPEN cr_x
WHILE 1 = 1
BEGIN
FETCH next FROM cr_x INTO @table, @col, @type
IF @@fetch_status <> 0
BREAK;
SELECT @sql = 'insert into #Results (tableName, columnName, columnValue)
select @table, @col, ' + colNew + N'
from ' + quotename(@table) + '
where ' + colNew + N' LIKE @SearchStr
group by ' + colNew
FROM (
SELECT CASE
WHEN @type = 0 THEN 'cast(' + QUOTENAME(@col) + ' as nvarchar(max))'
when @type = 1 then '
case when SQL_VARIANT_PROPERTY(' + QUOTENAME(@col) + ',''BaseType'') in (''char'', ''nchar'',''varchar'', ''nvarchar'', ''text'', ''ntext'', ''xml'')
then cast(' + QUOTENAME(@col) + N' as nvarchar(max))
when SQL_VARIANT_PROPERTY(' + QUOTENAME(@col) + ',''BaseType'') IN (''datetime'', ''date'', ''smalldatetime'', ''datetime2'',''datetimeoffset'')
then convert(nvarchar(max), cast(' + QUOTENAME(@col) + ' as datetime), 121)
end
'
when @type = 2 then 'convert(varchar(30), ' + QUOTENAME(@col) + ', 121)' END AS colNew
) x
EXEC sp_executesql @sql
, N'@table sysname, @col sysname, @searchstr nvarchar(max)'
, @table = @table, @col = @col, @searchstr = @searchstr
END
CLOSE CR_X
DEALLOCATE CR_X
SELECT TOP 301110 *
FROM #Results
It checks the tables and columns with specific datatypes and for datetimes it does a bit of conversion as well as grouping to avoid too many rows. I added a little dummy table so you can verify various values.
It also handles sql_variant wrapping datatypes