|
Home > Archive > MS SQL Server > February 2006 > Attributes of Database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Attributes of Database
|
|
| Prasad 2006-02-14, 7:23 am |
| Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
Prasad
| |
| Uri Dimant 2006-02-14, 7:23 am |
| Take a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> Does anybody know how to get the following information for a particular
> database in SQL 2000
>
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
>
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
>
> TIA
> Prasad
>
| |
| Kalen Delaney 2006-02-14, 11:23 am |
|
Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"Prasad" <ekke_nikhil@yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@TK2MSFTNGP14.phx.gbl...
>
> Hi,
>
> Does anybody know how to get the following information for a particular
> database in SQL 2000
>
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
>
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
>
> TIA
> Prasad
>
>
| |
| Prasad 2006-02-15, 7:23 am |
| Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" < replies@public_newsg
roups.com> wrote in message
news:%23jMoCSYMGHA.2124@TK2MSFTNGP14.phx.gbl...
>
> Hi Prasad
>
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
>
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
>
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
>
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
>
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
>
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www. solidqualitylearning
.com
>
>
> "Prasad" <ekke_nikhil@yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@TK2MSFTNGP14.phx.gbl...
>
>
>
| |
|
| For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
---
http://milambda.blogspot.com/
| |
| Prasad 2006-02-15, 7:23 am |
| Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F- 8EE342A5C7E1@microso
ft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
>
> I'd hard-code the names, though.
>
>
> ML
>
> ---
> http://milambda.blogspot.com/
| |
|
|
| Razvan Socol 2006-02-15, 9:23 am |
| Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan
| |
| Prasad 2006-02-16, 7:23 am |
| Thanks Razvan
"Razvan Socol" <rsocol@gmail.com> wrote in message
news:1140015795.987952.21330@f14g2000cwb.googlegroups.com...
> Hi, Prasad
>
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
>
> Razvan
>
|
|
|
|
|