Listing db names from SQL script

Question

Post reply

Listing db names from SQL script

David Putman

Mr or Mrs. 500

Points: 541
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: 1000539 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)
Intro to Tally Tables and Functions

David Putman Mr or Mrs. 500 Points: 541 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

David Putman Mr or Mrs. 500 Points: 541 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

David Putman Mr or Mrs. 500 Points: 541 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 541 More actions · Answer 9

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