Excluding System Databases while excecuting sp_databases in SQL Server

Question

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

Answer 1

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')

Answer 2

Thanks Sambo.

I have a server's name/instance & I have to list the databases under that server. However, the 'SELECT...' query lists all the databases including those I don't have access to. But I want only those databases that I have access to. sp_databases is giving me the correct results, but along with sys Database names. I want to know how to filter those system database names that are being listed.