Help with a Select against sys.databases

Question

Post reply

Help with a Select against sys.databases

Jpotucek

SSCoach

Points: 18583
More actions
November 14, 2011 at 8:17 am

#247797

MS SQL Server 2005...
I am adding DDL Triggers to some of my Instances to alert me when a Database is added or dropped.
I've got that part ready to roll out.
What I am not sure about is how to do a select against sys.databases and narrow is down to Databases created today (or in the last couple of days) or perhaps ORDER_BY to put the Databases created last n the top of the list?
This is what I have so far..
SELECT [Name], create_date FROM sys.databases

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

SELECT

DATEADD(D , 0 , DATEDIFF(D , 1 , GETDATE())) AS CreatedSince

, create_date AS CreatedOn

, *

FROM

sys.databases

WHERE

create_date >= DATEADD(D , 0 , DATEDIFF(D , 1 , GETDATE()))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

P.S. Check the default trace, I think it holds that information (except maybe who did it).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What do you need the query for? The DDL trigger?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jpotucek SSCoach Points: 18583 More actions · Answer 4

The DDL Triggers emails me when Database is created. Doesnt' tell me which Database - just which Instance the database was created in.

I would then have to query sys.databases to find out the name of the database created.. and hopefully sort by or order by create date.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us (11/14/2011)
P.S. Check the default trace, I think it holds that information (except maybe who did it).

I can find it if it was restored, but not if it was made by the GUI, so I guess that's a no go!

Jpotucek SSCoach Points: 18583 More actions · Answer 6

SELECT

DATEADD(D , 0 , DATEDIFF(D , 1 , GETDATE())) AS CreatedSince

, create_date AS CreatedOn

, *

FROM

sys.databases

WHERE

create_date >= DATEADD(D , 0 , DATEDIFF(D , 1 , GETDATE()))

Gave me what I was looking for. If a Database was created today - Now I can query sys.databases to show me only Databases created today. I don't really care WHO created it.. just that it was created.

Thank you so much!!!!!!!!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Jpotucek (11/14/2011)
SELECT DATEADD(D , 0 , DATEDIFF(D , 1 , GETDATE())) AS CreatedSince , create_date AS CreatedOn , * FROM sys.databases WHERE
create_date >= DATEADD(D , 0 , DATEDIFF(D , 1 , GETDATE()))
Gave me what I was looking for. If a Database was created today - Now I can query sys.databases to show me only Databases created today. I don't really care WHO created it.. just that it was created.
Thank you so much!!!!!!!!!

Change 1 for 0 in the where.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jpotucek (11/14/2011)
The DDL Triggers emails me when Database is created. Doesnt' tell me which Database - just which Instance the database was created in.
I would then have to query sys.databases to find out the name of the database created.. and hopefully sort by or order by create date.

Err, no. Look at the EventData function that's available in DDL triggers. From that you can extract the actual command run, who ran it, from where, etc, etc, no need for querying anything

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 9

GilaMonster (11/14/2011)
Err, no. Look at the EventData function that's available in DDL triggers. From that you can extract the actual command run, who ran it, from where, etc, etc, no need for querying anything

for reference, i happen to have played a bit with the EventData;

here's the EventData for CREATE_DATABASE:

--the fields in the XML depend on the event itself

--see http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd

--this is the set for the CREATE_DATABASE

DECLARE

@EventType varchar(128),

@PostTime datetime,

@SPID int,

@ServerName varchar(128),

@LoginName varchar(128),

@DatabaseName varchar(128),

@TSQLCommand varchar(128)

--load our values to variables in case we need them

SELECT

@EventType = msg.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar(128)' ),

@PostTime = msg.value('(/EVENT_INSTANCE/PostTime)[1]' ,'datetime' ),

@SPID = msg.value('(/EVENT_INSTANCE/SPID)[1]' ,'int' ),

@ServerName = msg.value('(/EVENT_INSTANCE/ServerName)[1]' ,'varchar(128)' ),

@LoginName = msg.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)' ),

@DatabaseName = msg.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),

@TSQLCommand = msg.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(128)' )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jpotucek SSCoach Points: 18583 More actions · Answer 10

Thank you Gail and Lowell! maybe I'll have to play around with it a little but when I create my DDL trigger like this:

CREATE TRIGGER [Create_Database_Trigger]

ON ALL Server

FOR Create_Database -- Captures a Create Database Event

AS

-- Print the message to the user

PRINT 'Database Created on ';

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ''

, @recipients = '.Mylastname@email.com'

, @subject = 'Database Created on '

, @body = 'Database must be Added to the NetBackup Backup Scripts

*****This is only a test****'

, @importance = 'high';

DECLARE

@EventType varchar(128),

@PostTime datetime,

@SPID int,

@ServerName varchar(128),

@LoginName varchar(128),

@DatabaseName varchar(128),

@TSQLCommand varchar(128)

--load our values to variables in case we need them

SELECT

@EventType = msg.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar(128)' ),

@PostTime = msg.value('(/EVENT_INSTANCE/PostTime)[1]' ,'datetime' ),

@SPID = msg.value('(/EVENT_INSTANCE/SPID)[1]' ,'int' ),

@ServerName = msg.value('(/EVENT_INSTANCE/ServerName)[1]' ,'varchar(128)' ),

@LoginName = msg.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)' ),

@DatabaseName = msg.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),

@TSQLCommand = msg.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(128)' )

I get this:

Create failed for Database 'Admintest2'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4226.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot find either column "msg" or the user-defined function or aggregate "msg.value", or the name is ambiguous.

Database Created on Mail queued. (Microsoft SQL Server, Error: 4121)

Lowell SSC Guru Points: 323518 More actions · Answer 11

somewhere in there you need something like this: that was a partial from my own version of a DDL trigger:

DECLARE @msg XML

SET @msg = EVENTDATA()

--replace also msg. with @msg.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Do note that Lowell's code is a just a snippet, not the full code necessary.

Msg needs to come from the EventData function, it may need some XQuery. In short, completion left to the reader 😉

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

GilaMonster (11/14/2011)
Do note that Lowell's code is a just a snippet, not the full code necessary.
Msg needs to come from the EventData function, it may need some XQuery. In short, completion left to the reader 😉

That should be in the site's contract somewhere! :hehe:

Lowell SSC Guru Points: 323518 More actions · Answer 14

for reference, this DDL trigger worked for me;

ALTER TRIGGER [Create_Database_Trigger]

ON ALL Server

WITH EXECUTE AS 'sa'

FOR Create_Database -- Captures a Create Database Event

AS

DECLARE

@EventType varchar(128),

@PostTime datetime,

@SPID int,

@ServerName varchar(128),

@LoginName varchar(128),

@DatabaseName varchar(128),

@TSQLCommand varchar(128),

@mySubject varchar(300),

@msg XML

SET @msg = EVENTDATA()

--load our values to variables in case we need them

SELECT

@EventType = @msg.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar(128)' ),

@PostTime = @msg.value('(/EVENT_INSTANCE/PostTime)[1]' ,'datetime' ),

@SPID = @msg.value('(/EVENT_INSTANCE/SPID)[1]' ,'int' ),

@ServerName = @msg.value('(/EVENT_INSTANCE/ServerName)[1]' ,'varchar(128)' ),

@LoginName = @msg.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(128)' ),

@DatabaseName = @msg.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),

@TSQLCommand = @msg.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(128)' )

-- Print the message to the user

SET @mySubject = 'Database ' + @DatabaseName + ' Created on ' + @@SERVERNAME + '';

PRINT @mySubject

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBMail Profile'

, @recipients = 'lowell@somedomain.com'

, @subject = @mySubject

, @body = 'Database must be Added to the NetBackup Backup Scripts

*****This is only a test****'

, @importance = 'high';

--ENABLE TRIGGER [Create_Database_Trigger] ON ALL Server

--DISABLE TRIGGER [Create_Database_Trigger] ON ALL Server

--DROP TRIGGER [Create_Database_Trigger] ON ALL Server

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!