CODEWITHSUNDEEP
c#sql-server
Jay
Jay

Reputation: 23

Excluding System Databases while excecuting sp_databases in SQL Server

I am retrieving the database list by executing the sp_databases stored proc. Is there any way I can exclude system databases from this list? I do not want to use the query SELECT name FROM dbo.sysdatabases where dbid > 6.

Thanks

Upvotes: 2

Views: 4683

Answers (1)

Sam Saffron
Sam Saffron

Reputation: 131112

sp_databases takes no params.

The only thing you can do is use the INSERT EXEC pattern to insert into a table var and then select from the table var and exclude the dbs you want to exclude. Seems a bit messy, can you expand the context of this problem?

This works, but it is a little hacky:

create table #t (db_name varchar(255), db_size int, remarks text) 

insert #t 
exec sp_databases

select * from #t
where db_name not in ('master', 'model', 'tempdb', 'msdb')

Upvotes: 2

Related Questions