October 27, 2014 at 2:44 pm
Comments posted to this topic are about the item Checking All Date Columns in a Table(s)
November 17, 2014 at 3:59 am
Interesting idea Larry, thanks. Ques: would it be more efficient to do the MIN() and MAX() in one UPDATE?
November 17, 2014 at 9:36 am
Perhaps so - I wanted to make the dynamic SQL UPDATES clear, and it does run pretty efficiently as is, but . . . always room for improvement!
Thanks for the comment!
Larry
October 28, 2015 at 11:24 am
Larry, I'm trying to do something similar in a user DB, get the minimum dates in their tables to see if there's an opportunity to implement a data retention policy.
I had to add a provision to take schema into account. The updates fail if the table isn't in the default dbo schema.
Thanks for coding this!
Ken
select IDENTITY(INT, 1,1) AS [ID],SCHEMA_NAME(t.schema_id) + '.' + t.name AS Tbl,c.name AS Col
into #Scorecardtmp
from syscolumns c
/*Ken Trock 10/2015 - to get schema_id which is used in the min and max date updates. Will bomb otherwise if qualifying tables are not in dbo*/
INNER JOIN sys.tables t ON c.id = t.object_id
INNER JOIN sysobjects o ON o.id = c.id
where o.xtype = 'U' and c.xusertype in (40,58,61) and t.name like @TableMask and c.name <> 'RowInsertDate' and c.name <> 'LastModifiedDate'
order by t.name,c.name
September 15, 2016 at 7:07 am
Nifty script, thanks.
September 15, 2016 at 12:41 pm
I'm doing a data quality project now to look for oddities. So this is useful for me.
I modified the script so it includes schema, table, and column names in separate fields. That should make it easier to sort and find what you need. Also, I added [brackets] in case the table/column names contain spaces.
SET NOCOUNT ON;
DECLARE @TableMask VARCHAR(MAX)= '%multi%';
IF OBJECT_ID('tempdb..#DateResults') IS NOT NULL
DROP TABLE #DateResults;
IF OBJECT_ID('tempdb..#Scorecardtmp') IS NOT NULL
DROP TABLE #Scorecardtmp;
CREATE TABLE #DateResults
(ID INT,
SchemaName varchar(255),
TableName varchar(255),
ColumnName varchar(255),
Maxdt DATE,
Mindt DATE
);
SELECT IDENTITY( INT, 1, 1) AS ID,
s.Name AS SchemaName,
o.name AS TableName,
c.name AS ColumnName
INTO #Scorecardtmp
FROM syscolumns c
INNER JOIN sysobjects o
ON c.id = o.id
inner join sys.schemas S
on S.schema_id = O.uid
WHERE o.xtype = 'U'
AND c.xusertype IN(40, 58, 61)
--AND o.name LIKE @TableMask
AND c.name <> 'RowInsertDate'
AND c.name <> 'LastModifiedDate'
ORDER BY o.name,
c.name;
DECLARE @MaxRownum INT;
SET @MaxRownum =
(
SELECT MAX(ID)
FROM #Scorecardtmp
);
DECLARE @Iter INT;
SET @Iter = 1;
DECLARE
@SQLCmd VARCHAR(MAX)
, @Maxdt DATE
, @Mindt DATE
, @SchemaName varchar(max)
, @TableName VARCHAR(MAX)
, @ColumnName VARCHAR(MAX)
, @ID INT;
WHILE @Iter <= @MaxRownum
BEGIN
SELECT @ID = ID,
@SchemaName = s.SchemaName,
@TableName = s.TableName,
@ColumnName = s.ColumnName
FROM #Scorecardtmp s
WHERE ID = @Iter;
INSERT INTO #DateResults
(ID,
SchemaName,
TableName,
ColumnName
)
VALUES
(@Iter,
@SchemaName,
@TableName,
@ColumnName
);
SET @SQLCmd = 'UPDATE #DateResults SET Mindt = (SELECT min(['+@ColumnName+']) from ['+@SchemaName+'].['+@TableName+']) where ID = '+CAST(@Iter AS VARCHAR);
EXEC (@SQLCmd);
SET @SQLCmd = 'UPDATE #DateResults SET Maxdt = (SELECT max(['+@ColumnName+']) from ['+@SchemaName+'].['+@TableName+']) where ID = '+CAST(@Iter AS VARCHAR);
EXEC (@SQLCmd);
SET @Iter = @Iter + 1;
END;
SELECT
dr.SchemaName
,dr.TableName
,dr.ColumnName
,dr.Mindt
,dr.Maxdt
FROM #DateResults dr
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply