September 19, 2010 at 9:00 am
how can i find the database name in which table was altered/created today with table name in ('ssn_info','pf_info)'
September 19, 2010 at 9:21 am
You can check the default trace: http://www.sqlservercentral.com/articles/64547/
September 19, 2010 at 10:15 am
You can use the undocumented procedure sp_MSforeachdb and query sys.objects in each database and check the create_date and modify_date columns. Here is an example (that I didn’t check, so there might be a small mistake in it):
exec sp_MSforeachdb
'if exists (select * from ?.sys.objects where name in (''ssn_info'',''pf_info'')
and (create_date >=''20100919'' or modify_date >= ''20100919''))
select ''?'''
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 19, 2010 at 12:20 pm
open dbnameCursor
Fetch next from dbnameCursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--print @dbname
if exists (select name from sys.tables where name in('ssn_info','pf_info') and
create_date>=dateadd(day, datediff(day, 0, getdate()), 0))
print 'conf tables are created'
FETCH NEXT FROM dbnameCursor INTO @dbname
END
CLOSE dbnameCursor
DEALLOCATE dbnameCursor
From the above code print message is excuted for all databses no matter which dataase table is created, what am i doing wrong?
I would like to filter only those tables which are created today and print message has to be executed only in that db.
September 20, 2010 at 3:14 pm
Your code is always referencing the current db. You need to use dynamic SQL to allow a db name to be added to the code:
declare @sql varchar(4000)
open dbnameCursor
Fetch next from dbnameCursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--print @dbname
set @sql = '
if exists (select name from [' + @dbname + '].sys.tables where name in(''ssn_info'',''pf_info'') and
create_date>=dateadd(day, datediff(day, 0, getdate()), 0))
print ''conf tables were created'''
EXEC (@sql)
FETCH NEXT FROM dbnameCursor INTO @dbname
END
CLOSE dbnameCursor
DEALLOCATE dbnameCursor
Scott Pletcher, SQL Server MVP 2008-2010
September 20, 2010 at 7:06 pm
Here's a query that gets it from the default trace:
With cteObjectTypes AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'ObjectType'
),
cteEventSubClasses AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'EventSubClass'
)
SELECT
TE.[name],
I.ApplicationName,
I.BigintData1,
I.ClientProcessID,
I.ColumnPermissions,
I.DatabaseID,
I.DatabaseName,
I.DBUserName,
I.Duration,
I.EndTime,
I.Error,
I.EventSequence,
Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
I.FileName,
I.HostName,
I.IndexID,
I.IntegerData,
I.IsSystem,
I.LineNumber,
I.LoginName,
I.LoginSid,
I.NestLevel,
I.NTDomainName,
I.NTUserName,
I.ObjectID,
I.ObjectID2,
I.ObjectName,
Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
I.OwnerName,
I.ParentName,
I.Permissions,
I.RequestID,
I.RoleName,
I.ServerName,
I.SessionLoginName,
I.Severity,
I.SPID,
I.StartTime,
I.State,
I.Success,
I.TargetLoginName,
I.TargetLoginSid,
I.TargetUserName,
I.TextData,
I.TransactionID,
I.Type,
I.XactSequence
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id LEFT JOIN
cteEventSubClasses AS ESC ON
TE.trace_event_id = ESC.trace_event_id And
I.EventSubClass = ESC.subclass_value LEFT JOIN
cteObjectTypes AS OT ON
TE.trace_event_id = OT.trace_event_id AND
I.ObjectType = OT.subclass_value
WHERE
T.is_default = 1 AND
TE.NAME IN ('Object:Created','Object:Altered') AND
I.ObjectName IN ('ssn_info','pf_info')
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply