Retrieve current SQL Server connection string within SQL query

Question

(this is an abstract question)

How can I get the current connection string from a T-SQL query?

I mean need for something like this:

Select ConnectionString From Something

and I get the result like this:

data source=HAMCKER-PC;initial catalog=CMMS;trusted_connection=true

Why I need this?

Actually I want to pass some extra parameters via my connection string, suppose the above connection to be something like this:

data source=HAMCKER-PC;initial catalog=CMMS;trusted_connection=true;Area=W9

I need that W9 in my queries.

Answer 1

To get data source use

SELECT @@SERVERNAME

initial catalog:

SELECT DB_NAME()

I could go look up trusted_connection as well but I don't see the point because you've already connected so you already know.

If you want to pass additional information using the connection string, your options are limited. You should probably use application name which can be accessed by SELECT APP_NAME() or Workstation ID. But these have meanings and I would be reluctant to 'hijack' them

You can also use CONTEXT_INFO to pass information msdn.microsoft.com/en-us/library/ms187768.aspx, msdn.microsoft.com/en-us/library/ms180125.aspx