information_schema question

Hi
I wanted to know what is the object type for information_schema. I could
not locate this object in any database.
Please share if anyone knows.
on all DB's
select * from sysobjects where lower(name)='information_schema'
Tks
Mangesh"INFORMATION_SCHEMA" isn't the name of an object, it's the name of a schema -
the namespace that contains the set of info schema views. You'll find that
the views are actually defined in Master.
--
David Portas
SQL Server MVP
--
"Mangesh Deshpande" wrote:
> Hi
> I wanted to know what is the object type for information_schema. I could
> not locate this object in any database.
> Please share if anyone knows.
> on all DB's
> select * from sysobjects where lower(name)='information_schema'
> Tks
> Mangesh

information_schema question

Hi
I wanted to know what is the object type for information_schema. I could
not locate this object in any database.
Please share if anyone knows.
on all DB's
select * from sysobjects where lower(name)='information_schema'
Tks
Mangesh"INFORMATION_SCHEMA" isn't the name of an object, it's the name of a schema
-
the namespace that contains the set of info schema views. You'll find that
the views are actually defined in Master.
David Portas
SQL Server MVP
--
"Mangesh Deshpande" wrote:

> Hi
> I wanted to know what is the object type for information_schema. I could
> not locate this object in any database.
> Please share if anyone knows.
> on all DB's
> select * from sysobjects where lower(name)='information_schema'
> Tks
> Mangeshsql

information_schema question

Hi
I wanted to know what is the object type for information_schema. I could
not locate this object in any database.
Please share if anyone knows.
on all DB's
select * from sysobjects where lower(name)='information_schema'
Tks
Mangesh
"INFORMATION_SCHEMA" isn't the name of an object, it's the name of a schema -
the namespace that contains the set of info schema views. You'll find that
the views are actually defined in Master.
David Portas
SQL Server MVP
"Mangesh Deshpande" wrote:

> Hi
> I wanted to know what is the object type for information_schema. I could
> not locate this object in any database.
> Please share if anyone knows.
> on all DB's
> select * from sysobjects where lower(name)='information_schema'
> Tks
> Mangesh

Info on data replication on sql server 2000.

Hi,
I'm in need of detailed information on how data replication works on the
sql server 2000. If someone knows about a book or articles that
describes how data replication works with transactions, stored
procedures and/or merging databases I would like to be pointed in the
right direction.

/Zero_Addiction.Hi

This is a new release from MVP, Hilary Cotter, and I haven't had a chance to
read it myself yet, but it does says there are 45 pages of information on
internals!

http://www.nwsu.com/0974973602.html

John
"zero_addiction" <j-e@.mail.com> wrote in message
news:cmlng6$6mh$1@.news.cybercity.dk...
> Hi,
> I'm in need of detailed information on how data replication works on the
> sql server 2000. If someone knows about a book or articles that describes
> how data replication works with transactions, stored procedures and/or
> merging databases I would like to be pointed in the right direction.
> /Zero_Addiction.

Info about a running job step

Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
QuentinYou can use sp_help_job e.g.
exec msdb..sp_help_job
@.job_name = 'jobname ,
@.job_aspect = 'JOB'
Look at the current_execution_status and current_execution_step columns
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message
news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
Quentin|||Thanks Mr. Dentist.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efTLfj4VDHA.1816@.TK2MSFTNGP09.phx.gbl...
> You can use sp_help_job e.g.
> exec msdb..sp_help_job
> @.job_name = 'jobname ,
> @.job_aspect = 'JOB'
> Look at the current_execution_status and current_execution_step columns
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi group,
> I need to retrieve information about a running step, specifically its
> status. When a step is being executed, the EM always knows and shows so
> (under status showing Executing Job Step 'n (step name)'). However, I can
> not find such information in the system tables. where does EM get the
info
> from? How can I access this info with a query, or better yet, within a
> stored proc? Help is appreciated.
> Quentin
>

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?It is my understanding that _wa objects are system created statistics.
--
Thomas
"Sal Young" wrote:
> Does anyone knows what kind of indexes are the ones showing up under certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?It is my understanding that _wa objects are system created statistics.
--
Thomas
"Sal Young" wrote:

> Does anyone knows what kind of indexes are the ones showing up under certa
in
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?
It is my understanding that _wa objects are system created statistics.
Thomas
"Sal Young" wrote:

> Does anyone knows what kind of indexes are the ones showing up under certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?
|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexes

Hello,
Anyone knows how to determine if an index is being used.
For example: I have several tables which have multiple indexes.
I want to know which of them are not being used so I can delete them and
release some space.
Is there a stored procedure to get these statistics?
TIA
Regards,
Eduardo SicouretWhat version of SQL Server? SQL Server 2000 doesn't keep track of such infor
mation, so you'd have to
do it using a Profiler trace or similar. 2005 does, and you can look at the
information using some
of the new dynamic management views (dm_db_missing_indexes_%).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl..
.
> Hello,
> Anyone knows how to determine if an index is being used.
> For example: I have several tables which have multiple indexes.
> I want to know which of them are not being used so I can delete them and r
elease some space.
> Is there a stored procedure to get these statistics?
> TIA
> Regards,
> Eduardo Sicouret
>|||I'm sorry...
I'm using SQL Server 2000...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server? SQL Server 2000 doesn't keep track of such
> information, so you'd have to do it using a Profiler trace or similar.
> 2005 does, and you can look at the information using some of the new
> dynamic management views (dm_db_missing_indexes_%).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>|||You are in for a lot more work, then and the question is whether you want to
do this in the end. You
can use Profiler to catch the execution plans, save such a trace and parse t
he text for the
execution plans to see what indexes were used, compare that to the indexes i
n the database and see
which weren't used.
Also, I by mistake types the wrong name for the dynamic management views for
2005, it should be
sys.dm_db_index_usage_stats and possibly
dm_db_index_operational_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl..
.
> I'm sorry...
> I'm using SQL Server 2000...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje
> news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>|||I'm not sure what causes updates to that usage table. I've tested it by
doing queries against my tables, inspecting the execution plan and then
finding no usage entry for the index indicated by the execution plan...what
gives?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uan97RpfGHA.324@.TK2MSFTNGP02.phx.gbl...
> You are in for a lot more work, then and the question is whether you want
> to do this in the end. You can use Profiler to catch the execution plans,
> save such a trace and parse the text for the execution plans to see what
> indexes were used, compare that to the indexes in the database and see
> which weren't used.
> Also, I by mistake types the wrong name for the dynamic management views
> for 2005, it should be
> sys.dm_db_index_usage_stats and possibly
> dm_db_index_operational_stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>

Indexes

Hello,
Anyone knows how to determine if an index is being used.
For example: I have several tables which have multiple indexes.
I want to know which of them are not being used so I can delete them and
release some space.
Is there a stored procedure to get these statistics?
TIA
Regards,
Eduardo SicouretWhat version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have to
do it using a Profiler trace or similar. 2005 does, and you can look at the information using some
of the new dynamic management views (dm_db_missing_indexes_%).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Hello,
> Anyone knows how to determine if an index is being used.
> For example: I have several tables which have multiple indexes.
> I want to know which of them are not being used so I can delete them and release some space.
> Is there a stored procedure to get these statistics?
> TIA
> Regards,
> Eduardo Sicouret
>|||I'm sorry...
I'm using SQL Server 2000...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió
en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server? SQL Server 2000 doesn't keep track of such
> information, so you'd have to do it using a Profiler trace or similar.
> 2005 does, and you can look at the information using some of the new
> dynamic management views (dm_db_missing_indexes_%).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and
>> release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>|||You are in for a lot more work, then and the question is whether you want to do this in the end. You
can use Profiler to catch the execution plans, save such a trace and parse the text for the
execution plans to see what indexes were used, compare that to the indexes in the database and see
which weren't used.
Also, I by mistake types the wrong name for the dynamic management views for 2005, it should be
sys.dm_db_index_usage_stats and possibly
dm_db_index_operational_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> I'm sorry...
> I'm using SQL Server 2000...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió en el mensaje
> news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have
>> to do it using a Profiler trace or similar. 2005 does, and you can look at the information using
>> some of the new dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>|||I'm not sure what causes updates to that usage table. I've tested it by
doing queries against my tables, inspecting the execution plan and then
finding no usage entry for the index indicated by the execution plan...what
gives?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uan97RpfGHA.324@.TK2MSFTNGP02.phx.gbl...
> You are in for a lot more work, then and the question is whether you want
> to do this in the end. You can use Profiler to catch the execution plans,
> save such a trace and parse the text for the execution plans to see what
> indexes were used, compare that to the indexes in the database and see
> which weren't used.
> Also, I by mistake types the wrong name for the dynamic management views
> for 2005, it should be
> sys.dm_db_index_usage_stats and possibly
> dm_db_index_operational_stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> I'm sorry...
>> I'm using SQL Server 2000...
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> escribió en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such
>> information, so you'd have to do it using a Profiler trace or similar.
>> 2005 does, and you can look at the information using some of the new
>> dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them
>> and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>>
>