Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>sql

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
--
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Finding Indexes

Hi,
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
AndrewThat's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
>> Hi,
>> Does anyone have a script for or know the easiest way to list the indexes in
>> all tables (for all user databases) in sql server 2000?
>> Thanks,
>> Andrew|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'use [@.DB]; ' +
' select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
' order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd => 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:
>> Also, thinks like statistics and hypothetical indexes need to be
>> filtered out. Check out the INDEXPROPERTY function.

Finding Indexes

Hi,
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
Andrew
That's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>
|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd =
'use [@.DB]; ' +
'select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
'where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
'order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>
|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:[vbcol=seagreen]
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd =
> 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MGWhy is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MG|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
--
David Portas

Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.
yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
David Gugick
Quest Software
www.quest.com
|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want to
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fit
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can find
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and after
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
[vbcol=seagreen]
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally, a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the field.
> So, your example is not actually accurate in how big a row of the index (and
> it is further dependent on whether the index is clustered or non-clustered)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> will also show you if there is fragmentation in those pages and extents. In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can find
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.co...umented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
[vbcol=seagreen]
> I totally understand and am aware of the points mentioned. The main reason
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and after
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some where,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:

Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+
4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
David Gugick
Quest Software
www.quest.com|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want t
o
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fi
t
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field
.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can fin
d
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and afte
r
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
[vbcol=seagreen]
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will
fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally,
a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the fie
ld.
> So, your example is not actually accurate in how big a row of the index (
and
> it is further dependent on whether the index is clustered or non-clustered
)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up.
It
> will also show you if there is fragmentation in those pages and extents.
In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can f
ind
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
>|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.c...cumented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
[vbcol=seagreen]
> I totally understand and am aware of the points mentioned. The main reaso
n
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to
do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and af
ter
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some wher
e,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:
>

Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
--
David Gugick
Quest Software
www.quest.com|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want to
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:
> yodarules wrote:
> > Hey Guys,
> >
> > I would like to enumerate all the indexes in a table and find the
> > size of the index, when I mean the size not the total space occupied
> > but the size of each row of the index. Eg: Table EMP has id bigint,
> > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > would be used in later calculating the required size based on number
> > of records to be inserted.
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fit
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can find
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:
> yodarules wrote:
> > Hey Guys,
> >
> > I would like to enumerate all the indexes in a table and find the
> > size of the index, when I mean the size not the total space occupied
> > but the size of each row of the index. Eg: Table EMP has id bigint,
> > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > would be used in later calculating the required size based on number
> > of records to be inserted.
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and after
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally, a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the field.
> So, your example is not actually accurate in how big a row of the index (and
> it is further dependent on whether the index is clustered or non-clustered)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> will also show you if there is fragmentation in those pages and extents. In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can find
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
> > yodarules wrote:
> > > Hey Guys,
> > >
> > > I would like to enumerate all the indexes in a table and find the
> > > size of the index, when I mean the size not the total space occupied
> > > but the size of each row of the index. Eg: Table EMP has id bigint,
> > > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > > would be used in later calculating the required size based on number
> > > of records to be inserted.
> >
> > See "Estimating the Size of a Table with a Clustered Index" in BOL.
> >
> > --
> > David Gugick
> > Quest Software
> > www.quest.com
> >|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
> I totally understand and am aware of the points mentioned. The main reason
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and after
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some where,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:
> > A couple of thoughts on your question.
> >
> > 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> > extents. Based on the fill factor and size of your index SQL Server will fit
> > as many rows from your index within the page as it can.
> > 2) A non-clustered index always contains a reference to the key of the
> > clustered index or a pointer to a place in the heap if no clustered index
> > exists, in addition to the data in the non-clustered index. Additionally, a
> > varchar field does not always take up all 8 bytes. Varchars are variable
> > length and only stored with the bytes taken up by the value within the field.
> > So, your example is not actually accurate in how big a row of the index (and
> > it is further dependent on whether the index is clustered or non-clustered)
> > would be.
> >
> > I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> > use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> > will also show you if there is fragmentation in those pages and extents. In
> > SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> > dm_db_index_physical_stats also provides values for min,avg and max record
> > size. Which might be what you are looking for. I don't know if you can find
> > that data for SQL 2000.
> >
> > The article in BOL that David pointed you to is a good illustration of how
> > that calculation would be done manually for a particular table and its
> > indexes.
> >
> > HTH
> >
> > --
> > Ryan Powers
> > Clarity Consulting
> > http://www.claritycon.com
> >
> >
> > "David Gugick" wrote:
> >
> > > yodarules wrote:
> > > > Hey Guys,
> > > >
> > > > I would like to enumerate all the indexes in a table and find the
> > > > size of the index, when I mean the size not the total space occupied
> > > > but the size of each row of the index. Eg: Table EMP has id bigint,
> > > > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > > > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > > > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > > > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > > > would be used in later calculating the required size based on number
> > > > of records to be inserted.
> > >
> > > See "Estimating the Size of a Table with a Clustered Index" in BOL.
> > >
> > > --
> > > David Gugick
> > > Quest Software
> > > www.quest.com
> > >

Find tables w/o primary keys but w/ unique clustered indexes

Hi
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>

Find table/indexes size

Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:
> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>|||Thank you all! Just what I needed!
FireStarter

Find table/indexes size

Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.
look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.
|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:

> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>
|||Thank you all! Just what I needed!
FireStarter

Find table/indexes size

Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:

> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>|||Thank you all! Just what I needed!
FireStarter

find out all indexes for a table

Does the command "sp_helpindex table_name" finds out all
indexes related to this table? Thanks.
Yes.
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.
|||In a nutshell,
Yes
Rick Sawtell
MCT, MCSD, MCDBA
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.

find out all indexes for a table

Does the command "sp_helpindex table_name" finds out all
indexes related to this table? Thanks.Yes.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.|||In a nutshell,
Yes
Rick Sawtell
MCT, MCSD, MCDBA
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.

find indexes

I'm a new user of SQL Server.
What's the script to find out all indexes for tables and
views?
Thanks.
Hi,
sp_helpindex <table name or view name>
You can also use the below statement to get all the information of the
object (including index).
sp_help <table or view name>
Thanks
Hari
MCDBA
"Julia" <KQD02@.YAHOO.COM> wrote in message
news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
> I'm a new user of SQL Server.
> What's the script to find out all indexes for tables and
> views?
> Thanks.
|||Thanks, is it possible to get all indexes for all tables
and views in one script?

>--Original Message--
>Hi,
>sp_helpindex <table name or view name>
>You can also use the below statement to get all the
information of the
>object (including index).
>sp_help <table or view name>
>Thanks
>Hari
>MCDBA
>
>"Julia" <KQD02@.YAHOO.COM> wrote in message
>news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
>
>.
>
|||Hi,
Execute the below script in query analyzer
Use dbname
go
select substring(a.name,1,20) as Table_View_name,substring(b.name,1,20) as
Index_name,type as Object_type,indid
from sysobjects a, sysindexes b
where a.id=b.id
and type in ('U','V')
and indid between 1 and 254
-- If indid = 1 then Clustered index
-- indid >1 then Nonclustered index
Thanks
Hari
MCDBA
Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:1443901c444d2$9074c850$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks, is it possible to get all indexes for all tables
> and views in one script?
>
> information of the

find indexes

I'm a new user of SQL Server.
What's the script to find out all indexes for tables and
views?
Thanks.Hi,
sp_helpindex <table name or view name>
You can also use the below statement to get all the information of the
object (including index).
sp_help <table or view name>
Thanks
Hari
MCDBA
"Julia" <KQD02@.YAHOO.COM> wrote in message
news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
> I'm a new user of SQL Server.
> What's the script to find out all indexes for tables and
> views?
> Thanks.|||Thanks, is it possible to get all indexes for all tables
and views in one script?
>--Original Message--
>Hi,
>sp_helpindex <table name or view name>
>You can also use the below statement to get all the
information of the
>object (including index).
>sp_help <table or view name>
>Thanks
>Hari
>MCDBA
>
>"Julia" <KQD02@.YAHOO.COM> wrote in message
>news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
>> I'm a new user of SQL Server.
>> What's the script to find out all indexes for tables and
>> views?
>> Thanks.
>
>.
>|||Hi,
Execute the below script in query analyzer
Use dbname
go
select substring(a.name,1,20) as Table_View_name,substring(b.name,1,20) as
Index_name,type as Object_type,indid
from sysobjects a, sysindexes b
where a.id=b.id
and type in ('U','V')
and indid between 1 and 254
-- If indid = 1 then Clustered index
-- indid >1 then Nonclustered index
Thanks
Hari
MCDBA
Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:1443901c444d2$9074c850$a301280a@.phx.gbl...
> Thanks, is it possible to get all indexes for all tables
> and views in one script?
>
> >--Original Message--
> >Hi,
> >
> >sp_helpindex <table name or view name>
> >
> >You can also use the below statement to get all the
> information of the
> >object (including index).
> >
> >sp_help <table or view name>
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >"Julia" <KQD02@.YAHOO.COM> wrote in message
> >news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
> >> I'm a new user of SQL Server.
> >> What's the script to find out all indexes for tables and
> >> views?
> >> Thanks.
> >
> >
> >.
> >

find indexes

I'm a new user of SQL Server.
What's the script to find out all indexes for tables and
views?
Thanks.Hi,
sp_helpindex <table name or view name>
You can also use the below statement to get all the information of the
object (including index).
sp_help <table or view name>
Thanks
Hari
MCDBA
"Julia" <KQD02@.YAHOO.COM> wrote in message
news:143d001c444cd$ab9aa640$a601280a@.phx
.gbl...
> I'm a new user of SQL Server.
> What's the script to find out all indexes for tables and
> views?
> Thanks.|||Thanks, is it possible to get all indexes for all tables
and views in one script?

>--Original Message--
>Hi,
>sp_helpindex <table name or view name>
>You can also use the below statement to get all the
information of the
>object (including index).
>sp_help <table or view name>
>Thanks
>Hari
>MCDBA
>
>"Julia" <KQD02@.YAHOO.COM> wrote in message
> news:143d001c444cd$ab9aa640$a601280a@.phx
.gbl...
>
>.
>

find index for all tables and views in one script

Is it possible to get all indexes for all tables
and views in one script?Please don't multi-post; you have a thread going on elsewhere.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx
.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>|||Yup, you need to query sysobjects and sysindexes system tables.
Eric
Julia wrote:

> Is it possible to get all indexes for all tables
> and views in one script?
>
Eric Li
SQL DBA
MCDBA

find index for all tables and views in one script

Is it possible to get all indexes for all tables
and views in one script?Please don't multi-post; you have a thread going on elsewhere.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>|||Yup, you need to query sysobjects and sysindexes system tables.
Eric
Julia wrote:
> Is it possible to get all indexes for all tables
> and views in one script?
>
Eric Li
SQL DBA
MCDBA|||Please look my reply in your old post.
Thanks
Hari
MCDBA
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>