I want to get a list of all of the user databases from an mssql server instance. What's the best way to do this?
我想从mssql服务器实例获取所有用户数据库的列表。最好的方法是什么?
I know I can select from sys.databases, but I don't see any way to filter out system databases besides hardcoding a list of names to exclude.
我知道我可以从系统中选择。数据库，但我不认为有任何方法可以过滤掉系统数据库，除了硬编码一个名称列表来排除。
I need the script to work on 2000/2005 and 2008.
我需要在2000/2005和2008年使用这个脚本。
If the approach I listed above is the only way to go, what are list of names I should exclude? I don't know if 2005 or 2008 added any new system databases off the top of my head.
如果上面列出的方法是唯一的方法，那么我应该排除哪些名称呢?我不知道是2005年还是2008年添加了新的系统数据库。
8 个解决方案
#1
9
Was looking in to this again today and decided to profile what Management Studio was doing to populate the Object Explorer details.
我们今天再次查看了它，并决定分析Management Studio是如何填充Object Explorer细节的。
Turns out the solution Microsoft have implemented is pretty simplistic and boils down to the following:
微软实施的解决方案非常简单，归结为以下几点:
SELECT *
FROM master.sys.databases
WHERE Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0
Please note that this was performed using SSMS 2008R2 (10.50.4033.0).
请注意，这是使用SSMS 2008R2(10.50.4033.0)执行的。
#2
7
The first query will return a table with data regarding all of the databases on the instance:
第一个查询将返回一个包含关于实例上所有数据库的数据的表:
Select *
From sys.databases
From this table you'll notice you can narrow down the scope of data you're looking for by using the WHERE clause. For example, the following queries will essentially return the same result table (the one you're most likely looking for):
您将注意到，通过使用WHERE子句可以缩小正在查找的数据范围。例如，下面的查询将本质上返回相同的结果表(您最可能要查找的结果表):
Select *
From sys.databases
Where database_id > 5
Select *
From sys.databases
Where len(owner_sid)>1
These queries will work in SQL Server 2008 and 2012.
这些查询将在SQL Server 2008和2012中工作。
#3
4
This works in 2005, not 100% sure about the other versions but I think it will fly. It's a bit of a hack but might get you what you need:
这在2005年是可行的，不是百分之百的肯定其他版本，但我认为它会飞。这是一个小技巧，但可能会得到你需要的:
select * from sys.databases where len(owner_sid)>1
#4
4
On SQL Server 2008 R2 Express, looks like I cannot reliably use any of the above methods. INFORMATION_SCHEMA.SCHEMATA only shows me information in the current database, db_id (database_id) #5 is my first user database, and owner_sid on two of my user databases on one of my mirrored databases (running on SQL Server 2008 R2 Standard) shows owner_sid = 1 for my two most recently created databases. (PablolnNZ's comment above is correct: I did not set an explicit owner for those two databases so it still shows as having an owner of 'sa'.)
在SQL Server 2008 R2 Express上，看起来我不能可靠地使用上述任何一种方法。INFORMATION_SCHEMA。SCHEMATA只向我显示当前数据库中的信息，db_id (database_id) #5是我的第一个用户数据库，我的一个镜像数据库(运行在SQL Server 2008 R2标准上)上的两个用户数据库上的owner_sid = 1显示了我最近创建的两个数据库的owner_sid = 1。(PablolnNZ上面的评论是正确的:我没有为这两个数据库设置一个显式的所有者，所以它仍然显示为“sa”的所有者。)
The only reliable means I was able to use was the following:
我能使用的唯一可靠的手段是:
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
#5
3
As nasty as it sounds to hardcode things. The names and number of system databases has been fairly consistent for several versions of SQL. However, if that is too unpleasant you could semi-hardcode them into a table and then plug that into your query.
就像硬编码一样令人讨厌。对于几个版本的SQL来说，系统数据库的名称和数量是相当一致的。但是，如果这太令人不快，您可以将它们半硬编码到一个表中，然后将其插入到查询中。
#6
0
Not sure if you can offhand. One note -- on 2k you'll have to use master.dbo.sysdatabases and not master.sys.databases (which doesn't exist in 2k).
不知道你能不能马上搞定。注意一点——在2k时，你必须使用master.dbo。sysdatabases而不是master.sys。数据库(在2k中不存在)。
#7
0
This works on 2000 to 2008 R2
在2000年到2008年，R2
SELECT name
FROM dbo.sysdatabases
WHERE dbid > 5
#8
0
Starting with SQL Server 2008 you have access to a view called sys.databases which when joined with sys.server_principals can eliminate the databases owned by sa, which you can (most often) safely discern are the "system databases". Thus, allowing you to filter these items out.
从SQL Server 2008开始，您可以访问名为sys的视图。与sys连接的数据库。server_principal可以消除sa所拥有的数据库，您可以(通常)安全地识别它们是“系统数据库”。因此，允许您过滤这些项。
select
d.name
,d.database_id
from
sys.databases d
join
sys.server_principals p
on p.sid = d.owner_sid
where
p.name <> 'sa';
#1
9
Was looking in to this again today and decided to profile what Management Studio was doing to populate the Object Explorer details.
我们今天再次查看了它，并决定分析Management Studio是如何填充Object Explorer细节的。
Turns out the solution Microsoft have implemented is pretty simplistic and boils down to the following:
微软实施的解决方案非常简单，归结为以下几点:
SELECT *
FROM master.sys.databases
WHERE Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0
Please note that this was performed using SSMS 2008R2 (10.50.4033.0).
请注意，这是使用SSMS 2008R2(10.50.4033.0)执行的。
#2
7
The first query will return a table with data regarding all of the databases on the instance:
第一个查询将返回一个包含关于实例上所有数据库的数据的表:
Select *
From sys.databases
From this table you'll notice you can narrow down the scope of data you're looking for by using the WHERE clause. For example, the following queries will essentially return the same result table (the one you're most likely looking for):
您将注意到，通过使用WHERE子句可以缩小正在查找的数据范围。例如，下面的查询将本质上返回相同的结果表(您最可能要查找的结果表):
Select *
From sys.databases
Where database_id > 5
Select *
From sys.databases
Where len(owner_sid)>1
These queries will work in SQL Server 2008 and 2012.
这些查询将在SQL Server 2008和2012中工作。
#3
4
This works in 2005, not 100% sure about the other versions but I think it will fly. It's a bit of a hack but might get you what you need:
这在2005年是可行的，不是百分之百的肯定其他版本，但我认为它会飞。这是一个小技巧，但可能会得到你需要的:
select * from sys.databases where len(owner_sid)>1
#4
4
On SQL Server 2008 R2 Express, looks like I cannot reliably use any of the above methods. INFORMATION_SCHEMA.SCHEMATA only shows me information in the current database, db_id (database_id) #5 is my first user database, and owner_sid on two of my user databases on one of my mirrored databases (running on SQL Server 2008 R2 Standard) shows owner_sid = 1 for my two most recently created databases. (PablolnNZ's comment above is correct: I did not set an explicit owner for those two databases so it still shows as having an owner of 'sa'.)
在SQL Server 2008 R2 Express上，看起来我不能可靠地使用上述任何一种方法。INFORMATION_SCHEMA。SCHEMATA只向我显示当前数据库中的信息，db_id (database_id) #5是我的第一个用户数据库，我的一个镜像数据库(运行在SQL Server 2008 R2标准上)上的两个用户数据库上的owner_sid = 1显示了我最近创建的两个数据库的owner_sid = 1。(PablolnNZ上面的评论是正确的:我没有为这两个数据库设置一个显式的所有者，所以它仍然显示为“sa”的所有者。)
The only reliable means I was able to use was the following:
我能使用的唯一可靠的手段是:
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
#5
3
As nasty as it sounds to hardcode things. The names and number of system databases has been fairly consistent for several versions of SQL. However, if that is too unpleasant you could semi-hardcode them into a table and then plug that into your query.
就像硬编码一样令人讨厌。对于几个版本的SQL来说，系统数据库的名称和数量是相当一致的。但是，如果这太令人不快，您可以将它们半硬编码到一个表中，然后将其插入到查询中。
#6
0
Not sure if you can offhand. One note -- on 2k you'll have to use master.dbo.sysdatabases and not master.sys.databases (which doesn't exist in 2k).
不知道你能不能马上搞定。注意一点——在2k时，你必须使用master.dbo。sysdatabases而不是master.sys。数据库(在2k中不存在)。
#7
0
This works on 2000 to 2008 R2
在2000年到2008年，R2
SELECT name
FROM dbo.sysdatabases
WHERE dbid > 5
#8
0
Starting with SQL Server 2008 you have access to a view called sys.databases which when joined with sys.server_principals can eliminate the databases owned by sa, which you can (most often) safely discern are the "system databases". Thus, allowing you to filter these items out.
从SQL Server 2008开始，您可以访问名为sys的视图。与sys连接的数据库。server_principal可以消除sa所拥有的数据库，您可以(通常)安全地识别它们是“系统数据库”。因此，允许您过滤这些项。
select
d.name
,d.database_id
from
sys.databases d
join
sys.server_principals p
on p.sid = d.owner_sid
where
p.name <> 'sa';