SQL Server中是否有一种唯一标识数据库的方法？

Is there any way to uniquely identify a database?

有没有办法唯一识别数据库？

If we were to copy a database to another machine, this instance is assumed to be different. I checked on master tables, but could not identify any information that can identify this.

如果我们要将数据库复制到另一台机器，则假定此实例不同。我检查了主表，但无法识别任何可以识别此信息的信息。

4 个解决方案

#1

You could make a table in it with a unique name, and simply do a query on that. It's a bit of a hack, sure, but it'd work...

您可以使用唯一名称在其中创建一个表，然后只需对其进行查询。这确实是一个黑客攻击，但它确实有效......

#2

You could put the information in an extended property associated with the database itself:

您可以将信息放在与数据库本身关联的扩展属性中：

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'AdventureWorks2008R2 Sample OLTP Database';
GO

http://msdn.microsoft.com/en-us/library/ms190243.aspx

In your case, I would use something like this:

在你的情况下，我会使用这样的东西：

EXEC sys.sp_addextendedproperty 
@name = N'UniqueID', 
@value = N'10156435463';

select objname, [name], [value] 
from fn_listextendedproperty (null, null, null, null, null, null, null)

#3

service_broker_guid in sys.databases comes pretty close to what you ask. It is a uniqueidentfier generated when the database is created and is preserved as the database is moved around (detach and attach, backup and restored, server rename etc). It can be explicitly changed with ALTER DATABASE ... SET NEW_BROKER;.

sys.databases中的service_broker_guid非常接近您的要求。它是在创建数据库时生成的唯一事件，并在数据库移动时保留（分离和附加，备份和还原，服务器重命名等）。可以使用ALTER DATABASE ... SET NEW_BROKER;显式更改它。

#4

Create a scalar function that returns an ID/Version number:

创建一个返回ID /版本号的标量函数：

create function fnGetThisDBID() returns varchar(32) as begin
    return ('v1.1,origin=server1')
end 

select 'version is: ' + dbo.fnGetThisDBID()

#1