SELECT from COLUMN if exists

  • I am trying to search through many tables to see if a certain person created or updated anything within a certain amount of time. Most tables have both a CREATEDON (datetime) and a CHANGEDON (datetime) field, but not all of them. What I am trying to do is create a WHERE statement that basically says:

    SELECT *

    From TABLENAME

    WHERE  (If the column exists use CREATEON ) = '2020-01-01'

    OR (if the column exists use CHANGEDON) = '2020-01-01'

    This is what I have so far, but it is not working.

    SELECT *

    FROM TABLENAME

    WHERE CASE WHEN exists(SELECT 1

    FROM syscolumns

    WHERE name = 'DTCREATEDON' AND id = OBJECT_ID('TABLENAME'))

    THEN (SELECT column_name

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_SCHEMA = 'dbo'

    AND TABLE_NAME = 'TABLENAME'

    AND COLUMN_NAME = 'DTCREATEDON')

    ELSE NULL END = '2020-01-01'

    Does anyone have other ideas?

     

    Thank you,

     

    Amy

  • Use this to generate the SELECT statements to review.  After you've verified the statements, run them if / as you see fit.

    DECLARE @DTCHANGEDON_found bit
    DECLARE @DTCREATEDON_found bit
    DECLARE @end_date datetime
    DECLARE @sql nvarchar(max)
    DECLARE @sql_template nvarchar(4000)
    DECLARE @start_date datetime
    DECLARE @tablename varchar(100)

    SET @start_date = '20200101'
    SET @end_date = '20200102'

    IF OBJECT_ID('tempdb.dbo.#tablenames') IS NOT NULL
    DROP TABLE #tablenames
    CREATE TABLE #tablenames (
    tablename varchar(100) NOT NULL PRIMARY KEY
    )
    INSERT INTO #tablenames VALUES
    ('TABLENAME1'), ('TABLENAME2'), ('TABLENAME3')

    SET @sql_template = N'($column$ >= ''$start_date$'' AND $column$ < ''$end_date$'')'

    DECLARE tables_cursor CURSOR FAST_FORWARD FOR
    SELECT tablename
    FROM #tablenames
    ORDER BY 1

    OPEN tables_cursor

    WHILE 1 = 1
    BEGIN
    FETCH NEXT FROM tables_cursor INTO @tablename
    IF @@FETCH_STATUS <> 0
    BREAK;
    IF EXISTS(SELECT 1 FROM sys.columns WHERE name = 'DTCREATEDON'
    AND OBJECT_NAME(object_id) = @tablename)
    SET @DTCREATEDON_found = 1
    ELSE
    SET @DTCREATEDON_found = 0
    IF EXISTS(SELECT 1 FROM sys.columns WHERE name = 'DTCHANGEDON'
    AND OBJECT_NAME(object_id) = @tablename)
    SET @DTCHANGEDON_found = 1
    ELSE
    SET @DTCHANGEDON_found = 0
    SET @sql = 'SELECT * FROM dbo.' + @tablename + ' WHERE '
    IF @DTCREATEDON_found = 1
    BEGIN
    SET @sql = @sql + REPLACE(REPLACE(REPLACE(@sql_template,
    '$column$', 'DTCREATEDON'),
    '$start_date$', CONVERT(varchar(10), @start_date, 112)),
    '$end_date$', CONVERT(varchar(10), @end_date, 112))
    END /*IF*/
    IF @DTCHANGEDON_found = 1
    BEGIN
    SET @sql = @sql +
    CASE WHEN @DTCREATEDON_found = 1 THEN ' OR ' ELSE '' END +
    REPLACE(REPLACE(REPLACE(@sql_template,
    '$column$', 'DTCHANGEDON'),
    '$start_date$', CONVERT(varchar(10), @start_date, 112)),
    '$end_date$', CONVERT(varchar(10), @end_date, 112))
    END /*IF*/
    IF @DTCREATEDON_found > 0
    OR @DTCHANGEDON_found > 0
    PRINT @sql
    END /*WHILE*/

    DEALLOCATE tables_cursor

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Does this get you anywhere close?

    WITH ColumnInfo
    AS (SELECT Sch = SCHEMA_NAME(t.schema_id)
    ,TableName = t.name
    ,ColumnName1 = MAX(c.name)
    ,ColumnName2 = MIN(c.name)
    FROM sys.tables t
    JOIN sys.columns c
    ON c.object_id = t.object_id
    WHERE c.name IN ('DTCHANGEDON', 'DTCREATEDON')
    GROUP BY SCHEMA_NAME(t.schema_id)
    ,t.name)
    SELECT SomeSQL = CONCAT(
    'select * from '
    ,ColumnInfo.Sch
    ,'.'
    ,ColumnInfo.TableName
    ,' WHERE '
    ,ISNULL(ColumnInfo.ColumnName1, ColumnInfo.ColumnName2)
    ,' = ''20200101'''
    )
    FROM ColumnInfo;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • I would be inclined to generate the SELECTS. Something like:

    SELECT 'SELECT * FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME +
    ' WHERE' +
    CASE WHEN CREATEDON IS NOT NULL THEN ' CREATEON = ''20200101''' ELSE '1=2' END +
    CASE WHEN CHANGEDON IS NOT NULL THEN ' OR CHANGEDON = ''20200101''' ELSE ' OR 1=2' END
    FROM
    (
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('CREATEON','CHANGEDON')
    AND DATA_TYPE LIKE 'date%'
    ) S
    PIVOT
    (
    MAX(COLUMN_NAME)
    FOR COLUMN_NAME IN ([CREATEON],[CHANGEDON])
    ) P;
  • Ken,

    I like this solution. My big question is once you have a list of select statements in your results, how do you execute them? I can look it up if you don't have time, but I wanted to let you know I appreciate the help. Very clever code.

    Amy

     

  • No worries, Ken. I figured it out. Stay safe!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply