Solved: SQL Syntax

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

draceplace

Thread Starter
Joined
Jun 8, 2001
Messages
2,583
When using a "USE" statement can you enumerate the database your are using?

USE MyBestDB
SELECT Crap1, Crap2
FROM dbo.CrapTable

I would like the query to pick up the DB name with out hard coding
USE MyBestDB
SELECT Crap1, Crap2, 'MyBestDB' as DB_Name FROM dbo.CrapTable
 
Joined
Apr 29, 2006
Messages
4,393
Did you solve this Dan? I've never done it, but I would think you could grab the first line, trim "Use" and add it as a parameter to your select statement. But I really have no idea. Please post back if you figure it out.
 

draceplace

Thread Starter
Joined
Jun 8, 2001
Messages
2,583
Thanks for the reply Chris, no solution yet. I actually haven't tried anything. Was hoping someone would enlighten me! Not sure how to 'grab' a line of SQL while running the Query. Interesting idea.

My goal is: I have 20 identical (structure not data) DBs, so just changing the USE statement gets me the report for a different one, I want the report to reflect were the data came from without hard coding.
 
Joined
Apr 29, 2006
Messages
4,393
Wait a minute...did you try DB_NAME() as bestdb ?

edit: Sorry...it would be DB_NAME() as db_name

It's an out of the box sql function. I think you could just add that to all of your queries and have the db name produced in a column with no worries.
 

draceplace

Thread Starter
Joined
Jun 8, 2001
Messages
2,583
Most Excellant Chris! I knew some one would enlighten me.

DB_NAME() as DB

Works Great!
 

draceplace

Thread Starter
Joined
Jun 8, 2001
Messages
2,583
One note to add. The DB_Name() AS DataBase works for this scenerio only when using the "USE" statement. If you use it with a qualified table names it reports 'master'. Without the 'AS' it reports "(No column name)" but the database name appears.

Thanks again, going to make my life easier.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top