Attributes of Database

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
PrasadTake 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
>|||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
>|||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_newsgroups.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...
>> 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
>>
>
>|||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/|||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@.microsoft.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/|||You're right. Sorry. What was I thinking...?
ML
--
http://milambda.blogspot.com/|||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|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> 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
>