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;
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy