March 4, 2013 at 1:30 pm
i dont know what I am askingfor is possible or not so just want to give a try here...:-)
I want to get a list of all databases and servers registered in SQL Central Management server and populate a sql table "A". I will have another table "B" with the database names and database supportgroups i.e, supportgroup1, supportgroup2 and supportgroup3.
After that I want to create a job that looks for the newly created databases and updates the tabe-A or creates a new table if needed , with data supportgroups and if there is no support group associated with a database, I get an email to update the table with right support group.
March 4, 2013 at 1:48 pm
newdba_sql (3/4/2013)
i dont know what I am askingfor is possible or not so just want to give a try here...:-)I want to get a list of all databases and servers registered in SQL Central Management server and populate a sql table "A". I will have another table "B" with the database names and database supportgroups i.e, supportgroup1, supportgroup2 and supportgroup3.
After that I want to create a job that looks for the newly created databases and updates the tabe-A or creates a new table if needed , with data supportgroups and if there is no support group associated with a database, I get an email to update the table with right support group.
This should get you started:
SELECT
d.name
,s.name
,ao.name
,ao.object_id
,ao.principal_id
,ao.schema_id
,ao.parent_object_id
,ao.type
,ao.type_desc
,ao.create_date
,ao.modify_date
,ao.is_ms_shipped
,ao.is_published
,ao.is_schema_published
FROM
sys.databases AS d
INNER JOIN sys.schemas AS s
ON d.database_id = s.principal_id
INNER JOIN sys.all_objects AS ao
ON s.schema_id = ao.schema_id
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply