How to findout if the column is Unique or Key?

---

I use sp_columns to retrieve colum information of a table.
But, it doesn't return the columns' index information such as Key or Unique..

How can I get these info along with those from sp_columns?

You want to take a look at sp_helpindex, sp_pkeys, sp_foreignkey stored procedures. Also, you want to take a look at info views (i.e. information_schema.*).

How to find Sql Server properties programatically

I am writing a program in .Net to retrieve properties of Sql Server.
How can I find the following properties without querying the database:

1. Replication enabled
2. Default language
3. Processors and threads running.

Thanks,
verveYou're probably looking for SQLDMO, which is a COM interface to MSSQL -
see Books Online for the details.

1. You can use the SQLDMO Replication object to enumerate replicated
databases, individual publications and articles etc.

2. Use the SQLServer.Language property

3. I'm not sure exactly what you want, but WMI might be a better option
for hardware and OS information. If by "threads" you really mean SQL
Server processes, then you can use the SQLServer.EnumProcesses method

In many cases, SQLDMO executes TSQL commands behind the scenes, so you
still need an authenticated connection to the server (and possibly a
user mapping in individual databases) to use it.

Simon|||Hi,
Thanks for the prompt reply but can also tell me which classes I should
be querying in WMI or which interface?

Thanks in advance :)
Verve|||The full WMI reference is on MSDN, but the TechNet scripting library
might be a faster way to get started:

http://www.microsoft.com/technet/sc...c/hwbavb03.mspx
http://www.microsoft.com/technet/sc...d/pcthvb07.mspx

Simon

How to filter out unwanted nodes?

I would like to retrieve the values of ProductIDs nodes but the condition is to ignore any ProductIDs that is a child of <AlternativeState> node.

Please keep in mind that xml data comes from table and the value of ProductIDs could appear anywhere else as well so I am afraid using exact XPath is not a choice. The only time when ProductIDs is not returned when its a child of AlternativeState.

Currently I have something like this but it returns all of the productIDs from the xml instance

SELECT UsageID, xmlState.query('

for $productIDs in //*/ProductIDs

order by $productIDs

return string($productIDs)') AS ProductIDs

FROM Usage.UsageAnalysis

WHERE UsageID = 91923

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<ProductIDs>46420</ProductIDs>

<AlternativeState><!-- Ignore anything in this element-->

<OtherEvents>

<ProductIDs>46420</ProductIDs>

</OtherEvents>

<MarketSize>

<ProductIDs>46431,46440,46469,46470</ProductIDs>

</MarketSize>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<ProductIDs>2128,2459,46420,46440,46470</ProductIDs>

</MarketSize>

</CurrentState>

</StatsState>

</State>

In your sample XML the AlternativeState element does not have a ProductIDs child, only a grand child. So for that XML sample the expression

Code Snippet

for $productIDs in //*[not(self::AlternativeState)]/*/ProductIDs

order by $productIDs

return string($productIDs)

should filter out what you want to filter out. Using an ancestor axis test should solve the problem in general but that axis is not currently supported in XQuery for SQL Server 2005.