List of databases on MSDE instance
-
I have this query to list all databases on an instance of SQL Server 2000.
SELECT sSchema.Catalog_Name FROM INFORMATION_SCHEMA.SCHEMATA sSchema WHERE sSchema.Catalog_name <> 'tempdb'
However, when I run this on a MSDE instance, it only shows 5 of the 47 databases that reside on that instance. I guess this is probably because under MSDE the databases are auto closed.
Whats the "recommended" way to get a list of databases from an MSDE instance. Do I have to do what everyone says you shouldn't do and query the system tables directly?
This is part of a generic routine, so I'd like to keep it as consistent as possible across all our servers.
--------------------
Colt 45 - the original point and click interface
-
Here is a way to get a list of the db.
It will only display dbs the user has access to
SELECT name
FROM master.dbo.sysdatabases
WHERE
has_dbaccess(name) = 1
AND name NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY name;
Hope this helps
Elian Chrebor
myLittleTools.net :: web-based applications
http://www.mylittletools.net -
Did you already try with undocumented sp_MSforeachdb?
exec sp_MSforeachdb @command1 = "Print '?'"
That should print out names of all databases.
Unfortunately any fix, service pack or newer version could change behaviour of this procedure.
You could also take a look at the MS logic for the query that populates the list of databases in this proc and build your own...
hope this helps
Diederik.
-
Diederik, the problem with undoumented procedures is that they are undocumented and there is no Guarantee that the next sp or upgrade will not break the procedure. Been there done that and still have the bite marks to prove it.
Remember Murphy's Law:
If anything can go wrong, it will at the most inopportune time. And Murphy was an optimist.
-
Phil, have you tried the following from BOL.
sp_databases (T-SQL)
Lists databases that reside in the Microsoft® SQL Server™ installation or are accessible through a database gateway.
Syntax
sp_databases
Return Code Values
None
Result Sets
Column name Data type Description DATABASE_NAME sysname Name of the database. In SQL Server, this column represents the database name as stored in the sysdatabases system table. DATABASE_SIZE int Size of database, in kilobytes. REMARKS varchar(254) For SQL Server, this field always returns NULL. Remarks
In SQL Server, sp_databases returns the databases listed in the sysdatabases system table. Because some database management systems (DBMS) accessed by database gateways do not have the concept of a database, this stored procedure may return no rows if sent to a Microsoft Open Data Services - based gateway.
Database names that are returned can be used as parameters in the USE statement to change the current database context.
HTH
Mike
-
Phil, I have been unable to replicate the behaviors you are describing.
My understanding from BOL is that Sp_databases reads the system tables for theresult set.
EXEC sp-databases andCREATE TABLE #tmp ( dbname sysname, dbsize int, dbrmks varchar(255))
INSERT INTO #tmp EXEC sp_databases
SELECT * FROM #Tmp -- returns only non-auto close
DROP TABLE #tmp
Return the same result set for me even though some dbs are set to auto-close.
I know that this does not help
but there must be something else going on here.Creating a table and inserting into that table with a stored procedure should not return
a different result set from just executing the stored procedure.
Mike
-
I believe the problem is not autoclose but is the user permissions.
Information_schema as well as sp_databases display the information for which
has_dbaccess(name) = 1
therefore you will have to go for:
select name from master.dbo.sysdatabases
hth
* Noel -
Noel
I'm connecting with 'sa' privelages to run my query and sp_databases. So I guess that rules out access permissions.
--------------------
Colt 45 - the original point and click interface -
well, if that is the case, I can't seem to duplicate your scenario either
* Noel -
Ok, I don't really like posting huge long messages, but this is really weird.
Just to get all the specs out,
Server is running Windows 2000 Server with SQL Server Desktop Engine 8.00.194 (can't service pack the beast because it's a third-party app that breaks when you apply the service pack )
Server has 47 databases with all but master, msdb, model and tempdb set to auto close.
Client machine is Windows XP SP2, running SQL client tools with SP4 applied (ver 8.00.2026). Connecting to the server via Windows Authentication using an account that has sysadmin privelages.
Running this series statements as a single batch in Query Analyzer,
EXEC master.dbo.sp_databases
CREATE TABLE #db ( DATABASE_NAME sysname , DATABASE_SIZE int , REMARKS varchar(255) )
INSERT INTO #db EXEC master.dbo.sp_databases
SELECT * FROM #db
CREATE TABLE db ( DATABASE_NAME sysname , DATABASE_SIZE int , REMARKS varchar(255) )
INSERT INTO db EXEC master.dbo.sp_databases
SELECT * FROM db
EXEC master.dbo.sp_databases
DROP TABLE #db DROP TABLE db
Returns these results.
DATABASE_NAME DATABASE_SIZE REMARKS DBA 2688 master 11648 model 4096 msdb 32320 sn0_Main 7808 sn0_Trans 4096 snCall189912 4096 snCall200207 15360 snCall200208 34816 snCall200209 36864 snCall200210 45056 snCall200211 41984 snCall200212 34816 snCall200301 35840 snCall200302 55296 snCall200303 73728 snCall200304 65536 snCall200305 84992 snCall200306 68608 snCall200307 86016 snCall200308 83968 snCall200309 90112 snCall200310 92160 snCall200311 78720 snCall200312 66560 snCall200401 67584 snCall200402 77824 snCall200403 86016 snCall200404 73728 snCall200405 71680 snCall200406 76800 snCall200407 79872 snCall200408 74752 snCall200409 75776 snCall200410 81920 snCall200411 66560 snCall200412 79872 snCall200501 64512 snCall200502 80896 snCall200503 80640 snCall200504 43008 snLog1899 30720 snLog2004 1284096 snLog2005 265216 snProcess 10688 snSecurity 4096 tempdb 2816
(8 row(s) affected)
DATABASE_NAME DATABASE_SIZE REMARKS DBA 2688 master 11648 model 4096 msdb 32320 snCall200303 73728 snCall200304 65536 snProcess 10688 tempdb 2816
(8 row(s) affected)
(23 row(s) affected)
DATABASE_NAME DATABASE_SIZE REMARKS DBA 2688 master 11648 model 4096 msdb 32320 snCall200303 73728 snCall200304 65536 snCall200402 77824 snCall200406 76800 snCall200407 79872 snCall200408 74752 snCall200409 75776 snCall200410 81920 snCall200411 66560 snCall200412 79872 snCall200501 64512 snCall200502 80896 snCall200503 80640 snCall200504 43008 snLog1899 30720 snLog2005 265216 snProcess 10688 snSecurity 4096 tempdb 2816
(23 row(s) affected)
DATABASE_NAME DATABASE_SIZE REMARKS DBA 2688 master 11648 model 4096 msdb 32320 sn0_Main 7808 sn0_Trans 4096 snCall189912 4096 snCall200207 15360 snCall200208 34816 snCall200209 36864 snCall200210 45056 snCall200211 41984 snCall200212 34816 snCall200301 35840 snCall200302 55296 snCall200303 73728 snCall200304 65536 snCall200305 84992 snCall200306 68608 snCall200307 86016 snCall200308 83968 snCall200309 90112 snCall200310 92160 snCall200311 78720 snCall200312 66560 snCall200401 67584 snCall200402 77824 snCall200403 86016 snCall200404 73728 snCall200405 71680 snCall200406 76800 snCall200407 79872 snCall200408 74752 snCall200409 75776 snCall200410 81920 snCall200411 66560 snCall200501 64512 snCall200502 80896 snCall200503 80640 snCall200504 43008 snLog1899 30720 snLog2005 265216 snProcess 10688 snSecurity 4096 tempdb 2816
Why do I get different database counts, and why is there a difference between inserting into a permanent table as opposed to a temp table?
--------------------
Colt 45 - the original point and click interface -
Phil, wierd is not the word I would have choosen as it is not descriptive enough.
Two quick questions. first have you run a through check for correlations on the missing data? I ran a quick check and though I had found a correlation between the db maming conventions. Assuming db naming convention of snCallyyyymm where yyyy=year and mm=month. But it did not work out. It is interesting to note that when saving to a temp database that the log and security dbs are missing.
Second question goes to your original post when you stated that "best practices" state that you should not query system tables. My question is why not? BOL states that sp_databases is reading the system tables for the db names, sizes and remarks (with remarks always being NULL)
I don't want to hijack your thread but if you could answer this I would appreciate it perhaps starting a new thread.
Thanks
Mike
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply