Listing db names from SQL script

Question

Post reply

Listing db names from SQL script

David Putman

Mr or Mrs. 500

Points: 571
More actions
January 15, 2008 at 5:40 am

#182063

Here is a poser, I'd be grateful if you can answer this. How can I list the names of databases on a server, please?
The end result that I would like is a stored procedure that would list the database names from a SQL Server where a particular table name exists. If not then at least to be able to list all of the database names. I have looked in sysobjects and related tables but no success yet. sp_helpdb is not what I need although I wish that I could read the code for it!!
Any ideas please?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

sp_helpdb is not what I need although I wish that I could read the code for it!!

That part is the easy part... execute the following code with the results in the text mode...

USE Master

EXEC sp_HelpText 'sp_HelpDB'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Putman Mr or Mrs. 500 Points: 571 More actions · Answer 2

🙂 Thanks very much I now have just the code that I need also you have shown me how to see inside system stored procedures, many thanks Jeff.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Probably a completely inadequate solution... but I wanted a little experiment this morning:

CREATE TABLE #DBList(DBName NVARCHAR(100))

DECLARE @Tablename NVARCHAR(100)

DECLARE @Tsql NVARCHAR(MAX)

DECLARE @DBName NVARCHAR(100)

DECLARE Cur CURSOR FOR

SELECT NAME

FROM [sys].databases;

SET @Tablename = 'ProductPhoto'

OPEN Cur

FETCH NEXT FROM Cur INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Tsql = 'IF EXISTS (SELECT * FROM [' + @Dbname + '].[INFORMATION_SCHEMA].TABLES WHERE TABLE_NAME = '''+ @TableName + ''')

INSERT INTO #DBList (

[DBName]

) VALUES (''' + @DBName + ''')'

EXEC sp_executesql @Tsql

FETCH NEXT FROM Cur INTO @DBName

END

CLOSE Cur

DEALLOCATE cur

SELECT * FROM #DBList

DROP TABLE #DBList

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

David Putman Mr or Mrs. 500 Points: 571 More actions · Answer 4

Thank you for your trouble Grant, I am grateful for your help. It seems that select * from [sysdatabases] is at the heart of the solution that I need. Many thanks. DP

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

Just note, it's 'sys.databases' not 'sysdatabases'. You need to get in the habit of looking at the catalog views, not the 2000 system tables.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

David Putman Mr or Mrs. 500 Points: 571 More actions · Answer 6

Thanks Grant, my company provide code for SQL 7, 2000 and 2005 so it looks like I will have to code for both sys databases. Thanks for the guidance. DP

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Oh, that's likely to be a bit tougher then. I'm not sure how you'd do this in 7.0 (haven't touched it in years and years).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

This works with 7.0, 2000, and 2005

if object_id('tempdb..#tables','U') is not null begin drop table #tables end

go

create table #tables ( TABLES_FULL sysname not null primary key clustered )

go

declare @sqlnvarchar(4000)

set @sql =

'use [?] ;

if db_name() <> N''?'' return

print ''Database = ''+db_name()

insert into #tables

select

TABLES_FULL =

''[''+TABLE_CATALOG+''].[''+TABLE_SCHEMA+''].[''+TABLE_NAME+'']''

from

information_schema.tables

where

table_name in (''MyTable'' )'

exec sp_msforeachdb @sql

select * from #tables

David Putman Mr or Mrs. 500 Points: 571 More actions · Answer 9

Michael, many thanks for your code. I will look into sp_msforeachdb that look really useful. Thanks for replying. DP