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
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