March 17, 2020 at 6:38 pm
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
March 17, 2020 at 7:24 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 17, 2020 at 7:38 pm
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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 17, 2020 at 7:47 pm
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;
March 17, 2020 at 8:51 pm
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
March 17, 2020 at 9:05 pm
No worries, Ken. I figured it out. Stay safe!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply