How do I know about new databases add to the server?

Question

0.00/5 (No votes)

See more:

Hi,

I am pretty new to SQL server. I have done coding but not database programming.

My question is: When users create a project in their application, it creates one database on SQL server. These databases have same table structure. In sys.databases, the database_id and database name gets created.

We created a view where we put the database ID and table structure and then union all to get data from a particular table from all the databases.

Currently we are hardcoding the database id which is coming from sys.databases in our view.

My question is: how do I automate this?

So when the new database gets created, my query can put that database id and the table structure automatically.

One of my colleague suggested:

(1)Create a trigger on the sys.databases table, so that when a new row is inserted, do a select for the new inserted row, find out the database_id.
(2)Used the database_id that was found out in (1), to add a new UNION in all of the views of concerns.

I don't know how to do that...any suggestions?

Thanks,
Ritesh

What I have tried:

I have to see how triggers work. As I said I am pretty new to database programming.

Posted 1-Dec-16 12:59pm

Member 12882034

Updated 6-Dec-16 10:10am

v3

Add a Solution

Comments

PIEBALDconsult 1-Dec-16 19:35pm

Please do not ever add anything to a database that does not belong to you.

Member 12882034 1-Dec-16 19:42pm

I am not. All I want is: Whenever new database gets added, I want to make sure my code knows about it so I can add database Id and database name in my view.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dbrenth · Answer 1 · 2016-12-06T10:10:00

Your code can run the following script to get all the databases on your server.

SQL

select dbid, name from master.dbo.sysdatabases

Just put it in a refresh button click_event.

If you are doing this in a view on the database,

Since this is just like any other table, just join it to whatever you need to.

Good Luck
Brent