SQL Server SQL search entire database for partial datetime return [duplicate]

ttvkxqim 于 2023-06-28 发布在其他

关注(0)|答案(3)|浏览(107)

This question already has answers here:

How do I search an SQL Server database for a string? (16 answers)
Closed 14 days ago.

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

sql-server

来源：https://stackoverflow.com/questions/76465765/sql-search-entire-database-for-partial-datetime-return

3条答案

按热度按时间

5kgi1eie1#

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

赞(0）回复(0）举报 2023-06-28

oxf4rvwz2#

I can't say that I fully understand what you want to do. But I Think You're Trying To Achieve Something Like This

DECLARE @SearchStr nvarchar(100) = '2010-09-10' 
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue varchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @DataType nvarchar(50)

DECLARE CRSR CURSOR FOR
SELECT QUOTENAME(TBL.TABLE_SCHEMA) + '.' + QUOTENAME(TBL.TABLE_NAME), QUOTENAME(COLUMN_NAME), CLMN.DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES TBL INNER JOIN INFORMATION_SCHEMA.COLUMNS CLMN ON CLMN.TABLE_SCHEMA = TBL.TABLE_SCHEMA AND CLMN.TABLE_NAME = TBL.TABLE_NAME 
WHERE TBL.TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TBL.TABLE_SCHEMA) + '.' + QUOTENAME(TBL.TABLE_NAME) ), 'IsMSShipped' ) = 0
AND CLMN.DATA_TYPE IN ('char', 'datetime', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
OPEN CRSR
FETCH NEXT FROM CRSR
INTO @TableName, @ColumnName, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
/*I've added this here to avoid Type conversion errors. You can change it If you want*/
DECLARE @WhereClause nvarchar(500) = CASE WHEN @DataType IN('int') THEN  @ColumnName + ' = CAST(CAST(''' + @SearchStr + ''' AS DATETIME) AS INT)'
WHEN @DataType IN('decimal') THEN  @ColumnName + ' = CAST(CAST(''' + (@SearchStr) + ''' AS DATETIME) AS DECIMAL)'
WHEN @DataType ='datetime' THEN @ColumnName + ' = ''' + (@SearchStr) + ''''
ELSE @ColumnName + ' LIKE ' + ('''%' + @SearchStr + '%''') END

INSERT INTO @Results
    EXEC
    (
        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
        FROM ' + @TableName + ' (NOLOCK) ' +
        ' WHERE ' + @WhereClause
    )
FETCH NEXT FROM CRSR
INTO @TableName, @ColumnName, @DataType

END
CLOSE CRSR
DEALLOCATE CRSR

SELECT ColumnName, ColumnValue FROM @Results

赞(0）回复(0）举报 2023-06-28

ct2axkht3#

First of all DATETIME datatype is unprecise so :

SELECT CAST('2010-09-10 13:33:11.441' AS DATETIME)

Will return 2010-10-09 13:33:11.440

It is recomended to use DATETIME2 instead of DATETIME.

Second prefer to USE standard ISO SQL views like INFORMATION_SCHEMA.COLUMNS instaed of systeme views (sys. ). INFORMATION_SCHEMA limitis always objects to user's one and not user and systeme objetcs...

The right solution is :

DECLARE @DH CHAR(21) = FORMAT(CAST('2010-09-10 13:33:11.441' AS DATETIME), 'yyyyMMdd HH:mm:ss.fff');
DECLARE @SQL NVARCHAR(max) = N'';

SELECT @SQL = @SQL + N'SELECT ''' + C.TABLE_SCHEMA + '.' + C.TABLE_NAME + ''' AS TABLENAME, ''' 
       + COLUMN_NAME + ''' AS COLUMNAME, * FROM [' +
       C.TABLE_SCHEMA + '].[' + C.TABLE_NAME + '] WHERE [' + COLUMN_NAME +'] = ''' + @DH + ''';'
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       JOIN INFORMATION_SCHEMA.TABLES AS T
          ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
          AND C.TABLE_NAME = T.TABLE_NAME
WHERE  T.TABLE_TYPE = 'BASE TABLE'
  AND  C.DATA_TYPE = 'datetime';

  PRINT @SQL

EXEC (@SQL);

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server SQL search entire database for partial datetime return [duplicate]

3条答案

相关问题

热门标签

最新问答