1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: SQL Syntax

Discussion in 'Software Development' started by draceplace, Jan 15, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. draceplace

    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
     
  2. ckphilli

    ckphilli

    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.
     
  3. draceplace

    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.
     
  4. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,393
    Ok, let me marinate on it and fool around a bit. I'll get back to you.
     
  5. ckphilli

    ckphilli

    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.
     
  6. draceplace

    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!
     
  7. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,393
    Awesome! Glad I could help-
     
  8. draceplace

    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.
     
  9. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085322

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice