How to identify which version we have?

How do I tell if I am running SQL2K Enterprise Edition or Standard Edition? We have a 2-node cluster. Does that imply Enterprise Edition, or could Standard do that as well? Thanks for any info.

If it's SQL 2000, only Enterprise is supported in a cluster configuration...if SQL 2005, Standard is also supported.

You can run the following to find your the edition of the server you are running...just connect via Query Analyzer to the instance you want to verify and execute:

select SERVERPROPERTY('Edition')

|||Thanks, that was very helpful. We are kicking around upgrade questions. The price for 2005 Enterprise is very much higher than Standard, but because we have 2000 Enterprise we were wondering if we needed that version in 2005 flavor as well. It looks like, with just a dual-node cluster, 2005 Standard fills the functionality that 2000 Enterprise had at least for clustering. Of course we don't get online recovery, online indexing, etc, but at least we could move to SQL 2005 Standard, get similar clustering functionality to what we have now and save $50K. That would take a very big bite out of our budget.

How to identify which edition im i running?

Good day,
I would like to identify which EDITION of reporting services im running,
enterprise or standard. I know that you can check the version by going to
http://localhost/reportserver. but I want to know the edition.
thanks in advance
Oren ZipporiHi Oren,
The edition you are running is similar to the edition of SQL Server ou
are running it on.
I=2Ee., if you are running SQL Server Standard Edition, then your
Reporting Services Edition is Standard.
Kind regards,
Bj=F6rn|||Hello Oren,
You can do it programmatically like this using the webservice API:
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.ServerInfoHeaderValue = new ServerInfoHeader();
rs.ListChildren("/", false);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
-Chris
> Good day,
> I would like to identify which EDITION of reporting services im
> running,
> enterprise or standard. I know that you can check the version by going
> to
> http://localhost/reportserver. but I want to know the edition.
> thanks in advance
> Oren Zippori

How to identify which all SPs are accessing a given table ?

I don't know if I am in the right section of the forum. Please help me with this :

Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database.

Thanks

Prasad P

Hi,

the only reliable method to get the information is to search the Information Schemas for the information, there is an article about sp_depends which sounds like you would get reliable information from it, but you don′t. I didn′t check the behaviour in SQL2k5, but check this article to get deeper information:

http://b.wunder.home.comcast.net/16509.htm

HTH, Jens Suessmeyer.

|||Check out my blog site:

http://blogs.claritycon.com/blogs/the_englishman/archive/2006/02/09/197.aspx

After having the same problem, I wrote a blog containing a stored procedure which searches stored procedures for a text string using the syscomments system table (which stores the stored proc definitions). If you pass the table name into this procedure, it should get you in the correct direction.

Let me know if that solves your problem, or whether you need more assistance.

HTH|||? No, there's nothing built in. A quick and dirty way of figuring it out is: SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TEXT LIKE '%YourTableName%' This does have some issues due to especially large routines and routines that make use of dynamic SQL and concatenate names, but it may give you some indication. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Prasad Peesapati@.discussions..microsoft.com> wrote in message news:bdc2238e-c590-47ff-927a-5660b2f552db@.discussions.microsoft.com... I don't know if I am in the right section of the forum. Please help me with this : Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database. Thanks Prasad P

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:
> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> > Hi,
> >
> > I want to identify, when the values are modified/updated on the particular
> > row in a table, i am not using triggers in this table.
> >
> > Please advise me, any options in SQL Profiler Trace to find out this.
> >
> > rgds,
> > Soura
>
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:

> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
>
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
Soura
SouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura
|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:

> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
>
>

how to identify what consumes cpu in sql server

Hi all
I use 64 bit 2005 server with 8cpu and 8G of memory.
This server is accessed by large number of intensive or not so intensive programs.
I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good.
But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it.
If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good.
Task manager shows that all of it 75% attributed to sql server.
So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.

Thank you, Gene.

You could have other issues besides CPU that are causing CPU pressure, or it could be that your queries are very efficient, but they are being run so frequently as to cause CPU pressure. Try running these DMV queries to get a better handle on what is going on.

-- Check CPU Pressure

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

FROM sys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

-- Get Top 50 executed SP's ordered by avg worker time

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

--WHERE qt.dbid = 5 -- Filter by database

ORDER BY qs.total_worker_time/qs.execution_count DESC

-- Get Top 50 executed SP's ordered by calls/sec

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',

qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',

--qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'

, qt.dbid

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

--WHERE qt.dbid = 5 -- Filter by database

ORDER BY qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) DESC

|||

Or try the SQL 2005 Performance Dashboard, it's pretty powerful

http://www.sql-server-performance.com/bm_performance_dashboard_2005.asp

|||

Hi Glen

It's very interesting material you sent. Thank you so much. i will need time on Monday to analyze it.

Great technical stuff! Gene.

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMSShipped') = 0
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
have[vbcol=seagreen]
> defined
of[vbcol=seagreen]
have[vbcol=seagreen]
not[vbcol=seagreen]
> out
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> system
ObjectProperty[vbcol=seagreen]
> have
> of
> have
> not
this[vbcol=seagreen]
procs[vbcol=seagreen]
>

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
'IsMSShipped') = 0
--
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> >
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > >I am querying master..sysobjects table for user defined objects. I
have
> > > been relying on category field to show if a given object is a user
> defined
> > > object or system object. My understanding was that a category value
of
> > > zero (0) is a user object. Apparently this is not always true. I
have
> > > seen value 16 for some. Where this is documented if any ? BOL does
not
> > > talk about the values. Or may be there is a better way to find this
> out
> > > other than Category field.
> > >
> > > I appreciate in advance for any suggestions.
> > > Mac
> > >
> > >
> >
> > Are you looking for specific objects?
> >
> > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> > etc.
> >
> > I'm not sure if that will help you out or not.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > I was looking for user-stored procedures in the master db.
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> > can use the TYPE to filter the object types but to know whether it is
> system
> > defined or user defined object, I received an answer that
ObjectProperty
> > function can be used to find that out.
> >
> > "Rick Sawtell" <quickening@.msn.com> wrote in message
> > news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > >
> > > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > > >I am querying master..sysobjects table for user defined objects. I
> have
> > > > been relying on category field to show if a given object is a user
> > defined
> > > > object or system object. My understanding was that a category value
> of
> > > > zero (0) is a user object. Apparently this is not always true. I
> have
> > > > seen value 16 for some. Where this is documented if any ? BOL does
> not
> > > > talk about the values. Or may be there is a better way to find
this
> > out
> > > > other than Category field.
> > > >
> > > > I appreciate in advance for any suggestions.
> > > > Mac
> > > >
> > > >
> > >
> > > Are you looking for specific objects?
> > >
> > > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for
procs
> > > etc.
> > >
> > > I'm not sure if that will help you out or not.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> >
> >
>

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
Mac
You can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA
|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out
>
|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTIN E_NAME),
'IsMSShipped') = 0
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system[vbcol=seagreen]
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
have[vbcol=seagreen]
> defined
of[vbcol=seagreen]
have[vbcol=seagreen]
not
> out
>
|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in[vbcol=seagreen]
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTIN E_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> system
ObjectProperty[vbcol=seagreen]
> have
> of
> have
> not
this[vbcol=seagreen]
procs
>

How to Identify user defined data types

How can you tell which datatypes in a given database are user defined
(with a query, not by looking in Enterprise Manager)? This is for SQL
Server 2000.SELECT domain_name
FROM information_schema.domains

--
David Portas
SQL Server MVP
--|||Bruce (sandell@.pacbell.net) writes:
> How can you tell which datatypes in a given database are user defined
> (with a query, not by looking in Enterprise Manager)? This is for SQL
> Server 2000.

SELECT * FROM systypes WHERE xusertype > 255

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to identify unused databases

Hi Folks,
client company has around 25 sql servers .
Each server has got 5 to 10 databases.
Some of the databases are old.
We don't know whether any applications using old databases or not.
Is there any way we can find the unused databases in past 3 months or past 1
year
using some Lastupdatedatetime or last action on sql server system tables
using T-SQL scripts.
If no application is accessing the database and no actions performed on
that database from past 1 year then we need to delete that databases.
How to identify unused databases?
Any kind of help is greatly appreciated.
Thanks
KumarKumar wrote:
> Hi Folks,
> client company has around 25 sql servers .
> Each server has got 5 to 10 databases.
> Some of the databases are old.
> We don't know whether any applications using old databases or not.
> Is there any way we can find the unused databases in past 3 months or
> past 1 year
> using some Lastupdatedatetime or last action on sql server system
> tables using T-SQL scripts.
> If no application is accessing the database and no actions performed
> on that database from past 1 year then we need to delete that
> databases.
> How to identify unused databases?
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
You can create a trace on the server. Include only the SQL:StmtStarting
and RPC:Starting events. Include only the minimum number of columns:
EventClass, SPID, DatabaseID. You could exclude system databases like
master, msdb, and tempdb.
To cut down the rows a little, you could use SQL:BatchStarting, but if a
batch has "USE" statements and accesses more than one database, the
DatabaseID will only refer to the database that was used at execution
time.
If you let the trace run for a couple of hours (or a day or two), you
should have a pretty good picture of what databases were accessed on
each server.
Best to do this using a server-side trace which you can script from
Profiler using the File - Script Trace menu option and stopping manually
using sp_trace_setstatus.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to identify the Reporting Services Edition

Hi all, I need to know the reporting services, if it is Enterprise, Standard
or Developer. I found only documents that tell me how to identify the
version, but not the edition.
Anyone knows how to do this'
Thank you very much!!!
Alexandre Calderaro
Avanade Italy
MSCDBASorry,
After post the question I found the answer in the most obvious place:
Control Panel, Add Remove Programs...
Anyway
Thanks
"Alex" wrote:
> Hi all, I need to know the reporting services, if it is Enterprise, Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>|||For 2000, the version information is located at the bottom of the main page
of your Report Server
RTM: 8.00.743.00
SP1 : 8.00.878.00
SP2 : 8.00.1038.00
The edition information is stored in the following registry key:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Reporting
Services\ProductCode
Standard: {B19FEFE7-069D-4FC4-8FDF-19661EAB6CE4}
Enterprise: {33FE9EED-1976-4A51-A7AF-332D9BBB9400}
Developer: {2879CA50-1599-4F4B-B9EC-1110C1094C16}
Evaluation: {7C93251A-BFB4-4EB8-A57C-81B875BB12E4}
Med Bouchenafa
"Alex" <Alex@.discussions.microsoft.com> a écrit dans le message de news:
92C3C825-D8BB-43C5-9342-F4807D12D157@.microsoft.com...
> Hi all, I need to know the reporting services, if it is Enterprise,
> Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>

how to identify the fixpack level?

hi,
for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server?
is there any command, or any gui tool to identify the level?
tnksSET NOCOUNT ON
SELECT CONVERT(CHAR(25),@.@.SERVERNAME) AS 'SQL SERVER',
SUBSTRING(@.@.VERSION,23,4) AS 'PRODUCT VERSION',
SUBSTRING(@.@.VERSION,35,3) AS 'BUILD NUMBER',
CASE SUBSTRING(@.@.VERSION,35, 3)
WHEN '194' THEN 'NO SP'
WHEN '384' THEN 'SP1'
WHEN '534' THEN 'SP2'
WHEN '760' THEN 'SP3'

ELSE 'Unknown - may be a Hot-Fix version or script out of date'
END AS 'SERVICE PACK'
set nocount off|||thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??|||SP3 and SP3a have identical build numbers (760).

Also, you can use this:

SELECT
cast(@.@.microsoftversion / power(2, 24) as varchar(2)) + '.00.' + cast(@.@.microsoftversion & 0xffff as varchar(4)) as VERSION

To derive version info...

hmscott
thanks philio,
how about service pack 3a, is there an id also to distinguish it from sp3??

how to identify stored procedure dependencies?

Hello,
I need to find where an sp is called from another sp or how many sp's a main
sp is calling. Here is something that I tried with no luck. I know that
this sp is calling other sp's or is being called by other sp's. How can I
get a list of sp's related to this'
--this does not return anything for me even though I know there are
dependencies
select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
OBJECT_NAME([depid]) in (select specific_name from
information_schema.routines where routine_type = 'sp_Compare_Add')
order by Proce
Thanks,
Rich1. See what the undocumented procedure master..sp_MSdependencies tells
you. However, this may sometimes miss references.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
3. Run a SQL Profiler event trace.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
> Hello,
> I need to find where an sp is called from another sp or how many sp's a
> main
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Here is a query which will give you the listings for procedure to procedure
dependencies:
select object_name([id]) as [Procedure],
object_name([depid]) as [Depends on Procedure]
from dbo.[sysdepends]
where objectproperty([id], 'IsProcedure') = 1
and objectproperty([depid], 'IsProcedure') = 1
and objectproperty([id], 'IsMSShipped') = 0
Order by [id], [depid]
Note that there is the possibility due to late binding data could be missing
here
HTH-
--Tony
"Rich" wrote:

> Hello,
> I need to find where an sp is called from another sp or how many sp's a ma
in
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Thanks very much.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
This comment did the trick. I located what I needed.
"JT" wrote:

> 1. See what the undocumented procedure master..sp_MSdependencies tells
> you. However, this may sometimes miss references.
> 2. You can query the syscomments table like so:
> select object_name(id) as obj_name from syscomments where text like
> '%usp_mysp%'
> 3. Run a SQL Profiler event trace.
> http://msdn.microsoft.com/library/d...enethowto15.asp
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
>
>|||Hi , you can also do this
CREATE PROC sp_search_code
(
@.SearchStr varchar(100),
@.RowsReturned int = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object
name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',
'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) +
'''' AS 'Run this command to see the object text'
FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @.SearchStr + '%' AND
encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)
ORDER BY 'Object type', 'Object name'
SET @.RowsReturned = @.@.ROWCOUNT
END

How to identify one sql server installation to another

Hi All,
How can we identify one sql server isntallation to another installation? For
example, installed sql server into a station, later remove it, then
installed again. How can we differential this 2 different installations?
Best Regards,
EdwardThe old one isn't there anymore? Is this like playing twister with a
blindfold?
Explain what you are wanting a little clearer. Give us a specific example.
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Edward Low" wrote:

> Hi All,
> How can we identify one sql server isntallation to another installation? F
or
> example, installed sql server into a station, later remove it, then
> installed again. How can we differential this 2 different installations?
>
> Best Regards,
> Edward
>
>|||Edward
If you did uninstall of SQL Server , all keys,data will be removed from the
registry, actually i did not check it by myself , but I'm pretty sure. Hav
you check it by using REGEDIT command
"Edward Low" <wc_low@.hotmail.com> wrote in message
news:ukS$qs6fGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> How can we identify one sql server isntallation to another installation?
> For example, installed sql server into a station, later remove it, then
> installed again. How can we differential this 2 different installations?
>
> Best Regards,
> Edward
>|||Hi,
Thanks for reply. In fact, i want to know the unique key of the sql server
installation. For example, we know every hard drive have a volumn serial no
which is assigned each time the partition is formatted. So, is the sql
server have such unique key/serial no?
Regards,
Edward
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:959C73C3-0577-4128-B167-0433BC9C1541@.microsoft.com...
> The old one isn't there anymore? Is this like playing twister with a
> blindfold?
> Explain what you are wanting a little clearer. Give us a specific
> example.
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Edward Low" wrote:
>

How to identify most executed queries?

Hi All,
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don′t want to waste money on it. Any suggestion?
Thanks
Celio.
Run a trace and dump and import the trace files into a table and run some
queries.
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I dont want to waste money on it. Any suggestion?
> Thanks
> Celio.

How to identify most executed queries?

Hi All,
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don′t want to waste money on it. Any suggestion?
Thanks
Celio.Run a trace and dump and import the trace files into a table and run some
queries.
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I dont want to waste money on it. Any suggestion?
> Thanks
> Celio.

How to identify most executed queries?

Hi All,
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don´t want to waste money on it. Any suggestion?
Thanks
Celio.Run a trace and dump and import the trace files into a table and run some
queries.
--
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I don´t want to waste money on it. Any suggestion?
> Thanks
> Celio.

How to identify Memory Leak caused by bad written stored procedures?

Does anyone has experience in monitoring Sql server memory leak? I am suspicious of some of the user stored procedures causing the memory leak. Can anyone who has such experience explaine how to find the offened stored procedures?

Thank you very much for any kind of suggestions and recommendations!

Unless you're talking about extended stored procedures, then stored procs themselves don't have memory leaks as such (like normal code), however, they may be doing things inefficiently, or you may have missing table indexes.

The best way to start is to use the SQL Profiler tool to monitor stored procedure execution and look at the duration field. If you capture results to a table, you can then select the slowest queries by ordering on the duration column descending. You should also group by the procedure name to look at the procedures executed most often.

The combination of the slowest queries, and those executed most often will give you the best place to start. From there it's a case of looking at the execution plan (in query analyser) of the procs and looking for ways to optimise the tsql or add/modify indexes.