SQL Server Performance Forum – Threads Archive
List all DB’s
Hello,Is there a way to get a list of your DB’s in QA? Thanks
J
J, sp_helpdb will list all of the databases. Dave
well itnot good to query system tables but u can check once this too
select * from master..sysdatabases
I would ask to go by Dave’s suggestion, rather than using the query and no need as you’ve pre-supplied SPs to perform the task. J, I suggest you to refer to the books online for more information and study on the basics of SQL SErver. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
When I run sp_Helpdb
I get the following error Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column ”, table ”; column does not allow nulls. INSERT fails.
The statement has been terminated. Am I missing anything? Satya,
How about using SCHEMATA instead of sysdatabases? select Catalog_Name from Information_Schema.SCHEMATA Madhivanan Failing to plan is Planning to fail
Information schema views are listed for that purpose only, you can use them. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
quote:Originally posted by Madhivanan
When I run sp_Helpdb
I get the following error Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column ”, table ”; column does not allow nulls. INSERT fails.
The statement has been terminated. Am I missing anything?
This happens because you have databases with no defined owner. Use sp_changedbowner to set a new owner, for example sp_changedbowner ‘sa’
sp_helpdb and other system SPs rely on the owner existing so thats why you get the NULL error above when none exist.
I get the following error Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column ”, table ”; column does not allow nulls. INSERT fails.
The statement has been terminated. Am I missing anything?
I changed the user
sp_changedbowner ‘sa’ Now sp_helpdb
gives new error
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column ‘owner’, table ‘tempdb.dbo.#spdbdesc000100000018’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Madhivanan Failing to plan is Planning to fail
You need to verify all databses on the server that they have an owner.
Yes I changed owner of all DB to sa
Now sp_helpdb displays all DBs
Madhivanan Failing to plan is Planning to fail
]]>