July 7, 2015 at 7:14 am
I Would appreciate some help with the following query...
I have a DB with all user tables containing CreateDate, CreateUser, ModifiedDate, Modified User.
I need the Table name, row count, Min Create Date, Min Create User, Max Create Date, Max Create Date user
I need to be able to run this over all tables in a DB, but I am struggling with the best way to approach it.
Any suggestions greatly appreciated.
Thanks
July 7, 2015 at 7:20 am
It cannot be a query, because when you use a "select" statement, the table name cannot be a variable.
I would get a list of the tables from one of the system views like one of the INFORMATION_SCHEMA views into a temp table, then loop through those building a dynamic select for each table and saving or listing the results.
July 7, 2015 at 8:07 am
DJH-445911 (7/7/2015)
I Would appreciate some help with the following query...I have a DB with all user tables containing CreateDate, CreateUser, ModifiedDate, Modified User.
I need the Table name, row count, Min Create Date, Min Create User, Max Create Date, Max Create Date user
I need to be able to run this over all tables in a DB, but I am struggling with the best way to approach it.
Any suggestions greatly appreciated.
Thanks
The only approach to get this working is AFAIK to use a loop of some kind (like sp_MSFOREACHTABLE) and within this loop you use dynamic SQL to execute the query on each table. Something like this:
declare @sql_command nvarchar(1500)
set @sql_command = 'SELECT ''?'' as table_name, count(*) as nr_of_rows, MIN(create_date) as MIN_cr_date, MIN(create_user) as MIN_cr_user, MAX(create_date) as MAX_cr_date, MAX(create_user) as MAX_cr_user '
+ 'FROM ? '
+ 'ORDER BY 1'
exec sp_MSforeachtable @sql_command
July 7, 2015 at 8:55 am
The only approach to get this working is AFAIK to use a loop of some kind (like sp_MSFOREACHTABLE) and within this loop you use dynamic SQL to execute the query on each table.
The ONLY approach? 🙂
Something like this:
declare @sql_command nvarchar(1500)set @sql_command = 'SELECT ''?'' as table_name, count(*) as nr_of_rows, MIN(create_date) as MIN_cr_date, MIN(create_user) as MIN_cr_user, MAX(create_date) as MAX_cr_date, MAX(create_user) as MAX_cr_user '
+ 'FROM ? '
+ 'ORDER BY 1'
exec sp_MSforeachtable @sql_command
I think the logic may be incorrect. I think the OP wants the user associated with the max or min date, not the max or min user.
This may work. Certainly it's not elegant!
1. Run this to create the code you need. CAUTION: If there are multiple rows with the same dates you may not get the correct results.
SELECT
'INSERT INTO #Results (MinCreateDate, MinCreateUser, MaxCreateDate, MaxCreateUser, MinModifyDate, MinModifyUser, MaxModifyDate, MaxModifyUser)
SELECT A.MinDate, A.CreateUser, B.MaxDate, B.CreateUser, C.MinDate, C.ModifyUser, D.MaxDate, D.ModifyUser
FROM
(SELECT MinCreate.MinDate, X.CreateUser
FROM ' + TABLE_NAME + ' X
INNER JOIN
(SELECT MIN(CreateDate) MinDate
FROM ' + TABLE_NAME + ') MinCreate ON X.CreateDate = MinCreate.MinDate) A
CROSS APPLY
(SELECT MaxCreate.MaxDate, X.CreateUser
FROM ' + TABLE_NAME + ' X
INNER JOIN
(SELECT MAX(CreateDate) MaxDate
FROM ' + TABLE_NAME + ') MaxCreate ON X.CreateDate = MaxCreate.MaxDate) B
CROSS APPLY
(SELECT MinModify.MinDate, X.ModifyUser
FROM ' + TABLE_NAME + ' X
INNER JOIN
(SELECT MIN(ModifiedDate) MinDate
FROM ' + TABLE_NAME + ') MinModify ON X.ModifiedDate = MinModify.MinDate) C
CROSS APPLY
(SELECT MaxModify.MaxDate, X.ModifyUser
FROM ' + TABLE_NAME + ' X
INNER JOIN
(SELECT MAX(ModifiedDate) MaxDate
FROM ' + TABLE_NAME + ') MaxModify ON X.ModifiedDate = MaxModify.MaxDate) D' + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
2. Create a temp table to hold the results:
CREATE TABLE #Results
(
MinCreateDate datetime,
MinCreateUser varchar(100),
MaxCreateDate datetime,
MaxCreateUser varchar(100),
MinModifyDate datetime,
MinModifyUser varchar(100),
MaxModifyDate datetime,
MaxModifyUser varchar(100)
)
3. Run the code from above to fill the table
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 7, 2015 at 9:00 am
Without having to rely on an undocumented procedure, you could use something like this:
IF OBJECT_ID('TablesInformation') IS NOT NULL
DROP TABLE TablesInformation;
CREATE TABLE TablesInformation(
name nvarchar(256),
rows char(20 ),
min_create_date datetime,
min_create_user varchar(18 ),
max_create_date datetime,
max_create_user varchar(18 )
);
DECLARE @Query nvarchar(MAX)
SET @Query = ( -- Table name, row count, Min Create Date, Min Create User, Max Create Date, Max Create Date user
SELECT 'INSERT INTO TablesInformation ' +
'SELECT ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''' AS Table_Name, ' +
'COUNT(*), MIN(Create_Date), MIN(Create_User), MIN(Create_Date), MIN(Create_User) ' +
'FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME <> 'TablesInformation'
FOR XML PATH('')
)
EXEC sp_executesql @Query
SELECT * FROM TablesInformation;
EDIT: Sorry, I got confused between threads. The idea is the same but I'll make some changes and post the correct code.
EDIT: Code corrected
July 7, 2015 at 9:02 am
Here is a framework to build on:
declare @TableName sysname,
@SQLCmd nvarchar(max);
declare TableNames insensitive cursor for
select
tab.name as TableName
from
sys.tables tab
order by
tab.name;
open TableNames;
fetch next from TableNames
into @TableName;
while @@FETCH_STATUS = 0
begin
select @SQLCmd = N'
with basedata as (
select
@CurrentTable CurrentTable,
rn1 = row_number() over (order by CreatedDate asc),
rn2 = row_number() over (order by CreatedDate desc),
CreatedDate,
CreatedUser
from
' + @TableName + N'
)
select *
from
basedata
where
rn1 = 1 or
rn2 = 1;';
exec sys.sp_executesql @SQLCmd, N'@CurrentTable sysname', @CurrentTable = @TableName;
fetch next from TableNames
into @TableName;
end
close TableNames;
deallocate TableNames;
July 7, 2015 at 9:41 am
Great, thank you for the replies.
Michael - Correct, I needed the associated users from the rows rather than min and max users, sorry if I wasnt clear on that. I used your code to get this working and it seems to run and will hopefuly satisfy the request, Thank you!
David
July 7, 2015 at 10:13 am
An improved version of my previous code. 😉
IF OBJECT_ID('TablesInformation') IS NOT NULL
DROP TABLE TablesInformation;
CREATE TABLE TablesInformation(
name nvarchar(256),
rows char(20 ),
min_create_date datetime,
min_create_user varchar(18 ),
max_create_date datetime,
max_create_user varchar(18 )
);
DECLARE @Query nvarchar(MAX);
WITH Row_Counts AS(
SELECT TABLE_SCHEMA + '.' + TABLE_NAME Table_Name, SUM(s.row_count) Row_Count
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.dm_db_partition_stats s ON s.object_id = OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME)
WHERE TABLE_NAME <> 'TablesInformation'
AND index_id IN(0,1)
GROUP BY TABLE_SCHEMA, TABLE_NAME
)
SELECT @Query = (
SELECT 'INSERT INTO TablesInformation ' +
'SELECT ''' + Table_Name + ''', ' + CAST( Row_Count AS varchar(15)) + ', ' +
'mn.Create_Date, mn.Create_User, mx.Create_Date, mx.Create_User ' +
'FROM (SELECT TOP 1 Create_Date, Create_User FROM ' + Table_Name + ' ORDER BY Create_Date) mn,' +
' (SELECT TOP 1 Create_Date, Create_User FROM ' + Table_Name + ' ORDER BY Create_Date DESC) mx;'
FROM Row_Counts
FOR XML PATH('')
)
EXEC sp_executesql @Query
SELECT * FROM TablesInformation;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply