Database Properties

There are multiple SQL Server instances on the company network, all are named uniquely, however, each one has the same database name, like ClientDB. I know there is a database ID for each one but I was wondering if there was a unique identifier that would work across instances? Now that I write this it sounds silly, how would a new instance know database names already out there?
I am asking because I am gathering information on all our databases like name, creator, size, last backed up, etc. into a table and it gets confusing because many records have the same database name. Perhaps there is an Instance ID or a database GUID somewhere, or do I just need to keep track of both the instance and database name to identify a record? Thanks for your help.

edited Jan 2 at 21:48

The Impaler

11.5k41441

asked Jan 2 at 20:12

Michael Conroy

There is a "machine name" and a "server name" property for SQL Server, maybe this is what you're looking for. docs.microsoft.com/en-us/sql/t-sql/functions/…

– Carter
Jan 2 at 20:16

add a comment |

edited Jan 2 at 21:48

The Impaler

11.5k41441

asked Jan 2 at 20:12

Michael Conroy

There is a "machine name" and a "server name" property for SQL Server, maybe this is what you're looking for. docs.microsoft.com/en-us/sql/t-sql/functions/…

– Carter
Jan 2 at 20:16

add a comment |

edited Jan 2 at 21:48

The Impaler

11.5k41441

asked Jan 2 at 20:12

Michael Conroy

sql sql-server database uniqueidentifier

edited Jan 2 at 21:48

The Impaler

11.5k41441

asked Jan 2 at 20:12

Michael Conroy

edited Jan 2 at 21:48

The Impaler

11.5k41441

asked Jan 2 at 20:12

Michael Conroy

edited Jan 2 at 21:48

The Impaler

11.5k41441

edited Jan 2 at 21:48

The Impaler

11.5k41441

edited Jan 2 at 21:48

The Impaler

11.5k41441

asked Jan 2 at 20:12

Michael Conroy

asked Jan 2 at 20:12

Michael Conroy

asked Jan 2 at 20:12

Michael Conroy

There is a "machine name" and a "server name" property for SQL Server, maybe this is what you're looking for. docs.microsoft.com/en-us/sql/t-sql/functions/…

– Carter
Jan 2 at 20:16

add a comment |

There is a "machine name" and a "server name" property for SQL Server, maybe this is what you're looking for. docs.microsoft.com/en-us/sql/t-sql/functions/…

– Carter
Jan 2 at 20:16

There is a "machine name" and a "server name" property for SQL Server, maybe this is what you're looking for. docs.microsoft.com/en-us/sql/t-sql/functions/…

– Carter
Jan 2 at 20:16

add a comment |

3 Answers
3

active

oldest

votes

You need to prepend the database name with the "host". The host is also known as:

"machine name", "hostname", "host name".

"server" or "server name", "computer name".

"box".

the "vm" or "vm name".

answered Jan 2 at 21:49

The Impaler

11.5k41441

add a comment |

It's called composite key. Since the database name alone isn't enough to uniquely identify it among your assets, you need to bring additional attributes to the table.

@@server_name to identify an instance of MS SQL Server on a box, along with the box name;

db_name() or a select from sys.databases to get database name(s) on that instance.

Together they comprise a key sufficiently unique for most cases. Note however that you still might get collisions if:

There are several AD domains in your network, and some servers have duplicate names in different domains. A bad network design decision by itself; I would try to switch from instance names to IP addresses here, or add the default_domain() function if your SQL Server versions support it;

You have clustered SQL instances. Not a collision per se, rather a duplicate in your records; just make sure you don't use cluster nodes' names, or keep a separate list of clusters / AlwaysOn instances to work around this. Haven't actually checked this myself, might be wrong here.

edited Jan 3 at 5:21

answered Jan 3 at 5:16

Roger Wolf

4,5951918

add a comment |

The service_broker_guid property assigned to each database (other than master and model) upon creation in SQL Server could satisfy your need. The service_borker_guid is a Global Unique Identifier, otherwise known as a UUID.

The Wikipedia page says the following about the chances that you will find two of the same value in, and out, of your network:

While the probability that a UUID will be duplicated is not zero, it is
close enough to zero to be negligible

The query below will grab the GUID you're looking for.

select @@servername as host_nm, name as db_nm, service_broker_guid

from master.sys.databases

where name in ('ClientDB')

Output:

+----------------+----------+--------------------------------------+

| host_nm        | db_nm    | service_broker_guid                  |

+----------------+----------+--------------------------------------+

| MY_SERVER_NAME | ClientDB | 9B7C1BF1-022D-423D-9780-88B555459868 |

+----------------+----------+--------------------------------------+

This question on the DBA site elaborates on GUID's if you want more detail on the subject.

answered Jan 3 at 6:01

tarheel

2,57983249

add a comment |

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012563%2fdatabase-properties%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

You need to prepend the database name with the "host". The host is also known as:

"machine name", "hostname", "host name".

"server" or "server name", "computer name".

"box".

the "vm" or "vm name".

answered Jan 2 at 21:49

The Impaler

11.5k41441

add a comment |

You need to prepend the database name with the "host". The host is also known as:

"machine name", "hostname", "host name".

"server" or "server name", "computer name".

"box".

the "vm" or "vm name".

answered Jan 2 at 21:49

The Impaler

11.5k41441

add a comment |

You need to prepend the database name with the "host". The host is also known as:

"machine name", "hostname", "host name".

"server" or "server name", "computer name".

"box".

the "vm" or "vm name".

answered Jan 2 at 21:49

The Impaler

11.5k41441

You need to prepend the database name with the "host". The host is also known as:

"machine name", "hostname", "host name".

"server" or "server name", "computer name".

"box".

the "vm" or "vm name".

answered Jan 2 at 21:49

The Impaler

11.5k41441

answered Jan 2 at 21:49

The Impaler

11.5k41441

answered Jan 2 at 21:49

The Impaler

11.5k41441

answered Jan 2 at 21:49

The Impaler

11.5k41441

add a comment |

It's called composite key. Since the database name alone isn't enough to uniquely identify it among your assets, you need to bring additional attributes to the table.

@@server_name to identify an instance of MS SQL Server on a box, along with the box name;

db_name() or a select from sys.databases to get database name(s) on that instance.

Together they comprise a key sufficiently unique for most cases. Note however that you still might get collisions if:

There are several AD domains in your network, and some servers have duplicate names in different domains. A bad network design decision by itself; I would try to switch from instance names to IP addresses here, or add the default_domain() function if your SQL Server versions support it;

You have clustered SQL instances. Not a collision per se, rather a duplicate in your records; just make sure you don't use cluster nodes' names, or keep a separate list of clusters / AlwaysOn instances to work around this. Haven't actually checked this myself, might be wrong here.

edited Jan 3 at 5:21

answered Jan 3 at 5:16

Roger Wolf

4,5951918

add a comment |

It's called composite key. Since the database name alone isn't enough to uniquely identify it among your assets, you need to bring additional attributes to the table.

@@server_name to identify an instance of MS SQL Server on a box, along with the box name;

db_name() or a select from sys.databases to get database name(s) on that instance.

Together they comprise a key sufficiently unique for most cases. Note however that you still might get collisions if:

There are several AD domains in your network, and some servers have duplicate names in different domains. A bad network design decision by itself; I would try to switch from instance names to IP addresses here, or add the default_domain() function if your SQL Server versions support it;

You have clustered SQL instances. Not a collision per se, rather a duplicate in your records; just make sure you don't use cluster nodes' names, or keep a separate list of clusters / AlwaysOn instances to work around this. Haven't actually checked this myself, might be wrong here.

edited Jan 3 at 5:21

answered Jan 3 at 5:16

Roger Wolf

4,5951918

add a comment |

It's called composite key. Since the database name alone isn't enough to uniquely identify it among your assets, you need to bring additional attributes to the table.

@@server_name to identify an instance of MS SQL Server on a box, along with the box name;

db_name() or a select from sys.databases to get database name(s) on that instance.

Together they comprise a key sufficiently unique for most cases. Note however that you still might get collisions if:

There are several AD domains in your network, and some servers have duplicate names in different domains. A bad network design decision by itself; I would try to switch from instance names to IP addresses here, or add the default_domain() function if your SQL Server versions support it;

You have clustered SQL instances. Not a collision per se, rather a duplicate in your records; just make sure you don't use cluster nodes' names, or keep a separate list of clusters / AlwaysOn instances to work around this. Haven't actually checked this myself, might be wrong here.

edited Jan 3 at 5:21

answered Jan 3 at 5:16

Roger Wolf

4,5951918

It's called composite key. Since the database name alone isn't enough to uniquely identify it among your assets, you need to bring additional attributes to the table.

@@server_name to identify an instance of MS SQL Server on a box, along with the box name;

db_name() or a select from sys.databases to get database name(s) on that instance.

Together they comprise a key sufficiently unique for most cases. Note however that you still might get collisions if:

There are several AD domains in your network, and some servers have duplicate names in different domains. A bad network design decision by itself; I would try to switch from instance names to IP addresses here, or add the default_domain() function if your SQL Server versions support it;

You have clustered SQL instances. Not a collision per se, rather a duplicate in your records; just make sure you don't use cluster nodes' names, or keep a separate list of clusters / AlwaysOn instances to work around this. Haven't actually checked this myself, might be wrong here.

edited Jan 3 at 5:21

answered Jan 3 at 5:16

Roger Wolf

4,5951918

edited Jan 3 at 5:21

answered Jan 3 at 5:16

Roger Wolf

4,5951918

answered Jan 3 at 5:16

Roger Wolf

4,5951918

answered Jan 3 at 5:16

Roger Wolf

4,5951918

add a comment |

The Wikipedia page says the following about the chances that you will find two of the same value in, and out, of your network:

While the probability that a UUID will be duplicated is not zero, it is
close enough to zero to be negligible

The query below will grab the GUID you're looking for.

select @@servername as host_nm, name as db_nm, service_broker_guid

from master.sys.databases

where name in ('ClientDB')

Output:

+----------------+----------+--------------------------------------+

| host_nm        | db_nm    | service_broker_guid                  |

+----------------+----------+--------------------------------------+

| MY_SERVER_NAME | ClientDB | 9B7C1BF1-022D-423D-9780-88B555459868 |

+----------------+----------+--------------------------------------+

This question on the DBA site elaborates on GUID's if you want more detail on the subject.

answered Jan 3 at 6:01

tarheel

2,57983249

add a comment |

The Wikipedia page says the following about the chances that you will find two of the same value in, and out, of your network:

While the probability that a UUID will be duplicated is not zero, it is
close enough to zero to be negligible

The query below will grab the GUID you're looking for.

select @@servername as host_nm, name as db_nm, service_broker_guid

from master.sys.databases

where name in ('ClientDB')

Output:

+----------------+----------+--------------------------------------+

| host_nm        | db_nm    | service_broker_guid                  |

+----------------+----------+--------------------------------------+

| MY_SERVER_NAME | ClientDB | 9B7C1BF1-022D-423D-9780-88B555459868 |

+----------------+----------+--------------------------------------+

This question on the DBA site elaborates on GUID's if you want more detail on the subject.

answered Jan 3 at 6:01

tarheel

2,57983249

add a comment |

The Wikipedia page says the following about the chances that you will find two of the same value in, and out, of your network:

While the probability that a UUID will be duplicated is not zero, it is
close enough to zero to be negligible

The query below will grab the GUID you're looking for.

select @@servername as host_nm, name as db_nm, service_broker_guid

from master.sys.databases

where name in ('ClientDB')

Output:

+----------------+----------+--------------------------------------+

| host_nm        | db_nm    | service_broker_guid                  |

+----------------+----------+--------------------------------------+

| MY_SERVER_NAME | ClientDB | 9B7C1BF1-022D-423D-9780-88B555459868 |

+----------------+----------+--------------------------------------+

This question on the DBA site elaborates on GUID's if you want more detail on the subject.

answered Jan 3 at 6:01

tarheel

2,57983249

The Wikipedia page says the following about the chances that you will find two of the same value in, and out, of your network:

While the probability that a UUID will be duplicated is not zero, it is
close enough to zero to be negligible

The query below will grab the GUID you're looking for.

select @@servername as host_nm, name as db_nm, service_broker_guid

from master.sys.databases

where name in ('ClientDB')

Output:

+----------------+----------+--------------------------------------+

| host_nm        | db_nm    | service_broker_guid                  |

+----------------+----------+--------------------------------------+

| MY_SERVER_NAME | ClientDB | 9B7C1BF1-022D-423D-9780-88B555459868 |

+----------------+----------+--------------------------------------+

This question on the DBA site elaborates on GUID's if you want more detail on the subject.

answered Jan 3 at 6:01

tarheel

2,57983249

answered Jan 3 at 6:01

tarheel

2,57983249

answered Jan 3 at 6:01

tarheel

2,57983249

answered Jan 3 at 6:01

tarheel

2,57983249

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu