extract database name from database connection string

Question

Hi all,

I'm trying to get the database name from connection string. It only works some of the time. Just wonder if I miss anything in my SQL script:

declare @dbconn varchar (500)

set @dbconn = 'Data Source=test-sql01d\NEW_NIDEV01;Initial Catalog=Restricted;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=false;'

select substring(@dbconn,(CHARINDEX('g=',@dbconn) + 2),CHARINDEX('T',right(@dbconn,79)))

if my database name is 'tested' then it worked but if my database name is something else like 'Restricted' then it came back with just Restr

Answer 1

Please try the following:

SQL

DECLARE @dbconn varchar (500)
 , @init_catalog VARCHAR(100)
 , @db_name VARCHAR(100)
 , @separator CHAR(1) = ';'
 , @separator2 CHAR(1) = '=';

SET @dbconn = 'Data Source=test-sql01d\NEW_NIDEV01;Initial Catalog=Restricted;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=false;';

SET @init_catalog =  TRY_CAST('<root><r>' + 
            REPLACE(@dbconn, @separator, '</r><r>') + 
            '</r></root>' AS XML).value('(/root/r[contains(./text()[1], "Initial Catalog")])[1]','VARCHAR(100)');
SELECT @init_catalog AS initial_catalog;

SET @db_name =  TRY_CAST('<root><r>' + 
            REPLACE(@init_catalog, @separator2, '</r><r>') + 
            '</r></root>' AS XML).value('(/root/r/text())[2]','VARCHAR(100)');
SELECT @db_name AS [db_name];

Output

+------------+
|  db_name   |
+------------+
| Restricted |
+------------+

Answer 2

The database name is not required in the connection string. So that is not a universal solution to your question.

Answer 3

thank you so much YitzhakKhabinsky-0887. It worked!

Answer 4

CREATE FUNCTION [dbo].[fn_GetDbNameFromConnectionString] (@ConnectionString VARCHAR(MAX) )

RETURNS sysname
AS
BEGIN

 DECLARE @Result sysname
 DECLARE @DBNameStart INT
 DECLARE @DBNameEnd INT
 DECLARE @DBNameLength INT

 SET @DBNameStart = (SELECT CHARINDEX('Initial Catalog=',@ConnectionString,0)+LEN('Initial Catalog='));
 SET @DBNameEnd = (SELECT CHARINDEX(';',@ConnectionString,@DBNameStart));
 SET @DBNameLength = (SELECT @DBNameEnd-@DBNameStart);

 SELECT @Result = SUBSTRING(@ConnectionString,@DBNameStart,@DBNameLength); 


 RETURN @Result

END
GO