How do I get the script to create a table & all the Indexes

In SS2K I could go to task and it would script the table, indexes and any other script that was set for that table. How do I do it with 2005?

Thanks

Have you looked at the Generate Script Wizard?|||

No. How do you use it?

Thanks

|||Right click on a database, select all tasks, generate scripts and then just follow the steps. If you are using SSMS Express it is not included.|||

That works for me.

Thanks

|||? You might want to check out this tool: http://www.sqlteam.com/publish/scriptio/ -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <VonSch@.discussions.microsoft.com> wrote in message news:102a22da-2ee1-4208-a1b1-79dfe66a70a6@.discussions.microsoft.com... In SS2K I could go to task and it would script the table, indexes and any other script that was set for that table. How do I do it with 2005? Thanks|||

I tried it and it works.

Thanks.

How do I find unique indexes in SQL-server 2005.

I am trying to get to grips with a database.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugman
select sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>

How do I find unique indexes in SQL-server 2005.

I am trying to get to grips with a database.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugmanselect sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>

How do I find unique indexes in SQL-server 2005.

I am trying to get to grips with a database.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugmanselect sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Thanks a lot(001frien@.gmail.com) writes:

Quote:

Originally Posted by

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

What was my INSERT-key up to last night? I missed to paste in the
query I was talking about. Here it is:

SELECT o.name, i.name,
col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
JOIN sysindexkeys ik ON ik.id = i.id
AND ik.indid = i.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(o.id, i.name, 'IsStatistics') = 0
AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
GROUP BY o.name, i.name
ORDER BY o.name, i.name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks a lot Erland

Erland Sommarskog wrote:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

>
What was my INSERT-key up to last night? I missed to paste in the
query I was talking about. Here it is:
>
SELECT o.name, i.name,
col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
JOIN sysindexkeys ik ON ik.id = i.id
AND ik.indid = i.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(o.id, i.name, 'IsStatistics') = 0
AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
GROUP BY o.name, i.name
ORDER BY o.name, i.name
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lotselect object_name(si.id), si.name from Sysindexes SI inner join
Sysojbects SO ON
(si.id = so.id) where xtype = 'u'
001fr...@.gmail.com wrote:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot|||This query will return a list of indexed columns that are NOT primary keys.
SELECT
TableName = cast( TABLE_NAME as varchar(50)),
ColumnName = cast( COLUMN_NAME as varchar(50))
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN dbo.sysindexes i
ON ( c.TABLE_NAME = object_name( i.id )
AND c.COLUMN_NAME = i.name
)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<001frien@.gmail.com> wrote in message news:1163193099.763199.289570@.k70g2000cwa.googlegroups
.com...
>
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
>
> Thanks a lot
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lot
select object_name(si.id), si.name from Sysindexes SI inner join
Sysojbects SO ON
(si.id = so.id) where xtype = 'u'
001fr...@.gmail.com wrote:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
|||This query will return a list of indexed columns that are NOT primary keys.
SELECT
TableName = cast( TABLE_NAME as varchar(50)),
ColumnName = cast( COLUMN_NAME as varchar(50))
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN dbo.sysindexes i
ON ( c.TABLE_NAME = object_name( i.id )
AND c.COLUMN_NAME = i.name
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
<001frien@.gmail.com> wrote in message news:1163193099.763199.289570@.k70g2000cwa.googlegr oups.com...
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lot
See the responses in microsoft.public.sqlserver.server
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"frien" <001frien@.gmail.com> wrote in message
news:1163196068.628580.97620@.b28g2000cwb.googlegro ups.com...
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
>
|||frien (001frien@.gmail.com) writes:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
Answered in comp.databases.ms-sqlserver. Please do not post the same
question, independetly to different newsgroups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

How do I drop indexes?

Hi
How do I drop all the indexes in a database in one go?
Thanks in advance
HWrite a cursor that loops over sysindexes and for each index executes the DROP INDEX command. Make
sure that you exclude indid 0 and 255. Also, for indexes created with a PRIMARY KEY and UNIQUE
constraint, you need to execute ALTER TABLE ... DROP CONSTRAINT instead of DROP INDEX.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H|||Hi,
I have wrote a script to do this, please login to MSDN and have a look. This
script will generate
This stored procedure will be useful to generate scripts for below
index categories for DROP as well as CREATE . Probably u can use the DROP
part.
a. Clustered Index associated with a Primary key constraint
b. Non Clustered Index associated with a primary key constraint
c. Non Clustered Index associated with a Unique Key constraint
d. Normal Clustered Index
e. Normal unique Clustered Index
f. Normal Non Clustered Index
g. Normal unique Non Clustered Index
http://www.microsoft.com/india/msdn/articles/196.aspx?
Thanks
Hari
MCDBA
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H

How do I drop indexes?

Hi
How do I drop all the indexes in a database in one go?
Thanks in advance
HWrite a cursor that loops over sysindexes and for each index executes the DR
OP INDEX command. Make
sure that you exclude indid 0 and 255. Also, for indexes created with a PRIM
ARY KEY and UNIQUE
constraint, you need to execute ALTER TABLE ... DROP CONSTRAINT instead of D
ROP INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx
.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H|||Hi,
I have wrote a script to do this, please login to MSDN and have a look. This
script will generate
This stored procedure will be useful to generate scripts for below
index categories for DROP as well as CREATE . Probably u can use the DROP
part.
a. Clustered Index associated with a Primary key constraint
b. Non Clustered Index associated with a primary key constraint
c. Non Clustered Index associated with a Unique Key constraint
d. Normal Clustered Index
e. Normal unique Clustered Index
f. Normal Non Clustered Index
g. Normal unique Non Clustered Index
http://www.microsoft.com/india/msdn/articles/196.aspx?
Thanks
Hari
MCDBA
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx
.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H

How do I drop indexes?

Hi
How do I drop all the indexes in a database in one go?
Thanks in advance
H
Write a cursor that loops over sysindexes and for each index executes the DROP INDEX command. Make
sure that you exclude indid 0 and 255. Also, for indexes created with a PRIMARY KEY and UNIQUE
constraint, you need to execute ALTER TABLE ... DROP CONSTRAINT instead of DROP INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H
|||Hi,
I have wrote a script to do this, please login to MSDN and have a look. This
script will generate
This stored procedure will be useful to generate scripts for below
index categories for DROP as well as CREATE . Probably u can use the DROP
part.
a. Clustered Index associated with a Primary key constraint
b. Non Clustered Index associated with a primary key constraint
c. Non Clustered Index associated with a Unique Key constraint
d. Normal Clustered Index
e. Normal unique Clustered Index
f. Normal Non Clustered Index
g. Normal unique Non Clustered Index
http://www.microsoft.com/india/msdn/articles/196.aspx?
Thanks
Hari
MCDBA
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H

How do I determine the table from index name

I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?
What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:

> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>
|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>

How do I determine the table from index name

I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
> >I ran a query to identify indexes with fragmentation problems, and it gives
> > me the index names. I cannot tell from the names what tables or views are
> > being indexed. I have been wondering through the system views, but so far
> > nothing jumps out at me (i.e. sys.table_indexes).
> > I don't think that this is something that has to be solved, since I rebuild
> > indexes once a week, but I would like to know: Starting wtih
> > sys.indexes.object_id, how can I determine the table or view name?
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and
>> for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes
>> a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it gives
>> > me the index names. I cannot tell from the names what tables or views are
>> > being indexed. I have been wondering through the system views, but so far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>> My question began with a defragmentation query that I found in an Sql
>> 2005
>> textbook, which produced a list of six suspects with OBJECT_NAME
>> (dt.object_id) = queue_messages_1003150619 or something similar. Since
>> that
>> certainly didn't match any table or view in the database, I assumed it
>> was
>> the name of an index. But the response from Dan Guzman includes a query
>> that
>> shows that it is actually the name of the table or view - which I just
>> said
>> doesn't exist. So now I'm really confused.
>> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books
>> Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC
>> SHOWCONTIG topic, and for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005
>> with sp2, this even takes a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it
>> >gives
>> > me the index names. I cannot tell from the names what tables or views
>> > are
>> > being indexed. I have been wondering through the system views, but so
>> > far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I
>> > rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?
>

How do I determine the table from index name

I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Onli
ne which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHO
WCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with
sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuil
d
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query tha
t
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:

> First, you might be re-inventing the wheel. You will find code in Books On
line which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC S
HOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 wit
h sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for
the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since th
at
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query t
hat
> shows that it is actually the name of the table or view - which I just sai
d
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>