sql 2005...sysdatabases in management studio?

Question

Post reply

sql 2005...sysdatabases in management studio?

b4better

SSChasing Mays

Points: 627
More actions
April 15, 2008 at 8:13 am

#154282

i'm trying to find the sysdatabases table under management studio, and can't seem to locate it.
i can see the sys.sysdatabases view, but it's different than the usual sysdatabases that i remember.
where did it go?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

The system table structure changed significantly in SQL 2005.

The sysdatabases table is no more.

b4better SSChasing Mays Points: 627 More actions · Answer 2

so if i wanted to create a trigger... i couldn't do it on the new table correct?

sys.databases

i simply get an error about invalid object.

how do i reference it?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Not only is it a bad idea to create a trigger on a system table, but the good engineers at Microsoft have prevented it.

You probably want to create a DDL trigger.

What are you actually trying to do?

b4better SSChasing Mays Points: 627 More actions · Answer 4

thanks again for the reply...

i've got jobs that run basic reports for each database size, mods, etc

and would like to have those jobs dropped if the database was dropped

so figure i could create a trigger on sysdatabases to where a delete takes

place, then the trigger would then remove the corresponding declared

database name (pulled from name column of sysdatabases) would then

pass into a 'remove job' + [name] script 'so to speak'

my simple automation.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

Michael Earl-395764

SSC Guru

Points: 53873

April 15, 2008 at 1:56 pm

#802787

Look up DDL triggers in books online.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

Specifically, you'll need a server scoped DDL trigger for DROP_DATABASE. Btw, I think the reason you got an error while trying to create a DML trigger on sys.databases is because it's view, not a table.

Greg

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Greg Charles (4/15/2008)
Specifically, you'll need a server scoped DDL trigger for DROP_DATABASE. Btw, I think the reason you got an error while trying to create a DML trigger on sys.databases is because it's view, not a table.

Perhaps - but sys being a read-only schema probably helps too:).....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?