information_schema permissions

[Pachydermitis]

Hi I need to see all the indexes in a database. The ID has dbo rights
to the database, but not to the master. I can't see anything in
INFORMATION_SCH EMA.CHECK_CONST RAINTS or
INFORMATION_SCH EMA.KEY_COLUMN_ USAGE
An sa ID for the master sees everything however.
Thanks for your help
Pachydermitis

[Erland Sommarskog]

Re: information_sch ema permissions

[posted and mailed, please reply in news]

Pachydermitis (dedejavu@hotma il.com) writes:[color=blue]
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCH EMA.CHECK_CONST RAINTS or
> INFORMATION_SCH EMA.KEY_COLUMN_ USAGE
> An sa ID for the master sees everything however.[/color]

Don't really see where INFORMATION_SCH EMA comes in. There is no
information about indexes there. Generally, I prefer using the
system tables, because they hold the complete set of information.

To see all indexes in a database (save those on system tables):

SELECT "table" = object_name(id) , name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(i d, name, 'IsHypothetical ') = 0
AND indexproperty(i d, name, 'IsStatistics') = 0
AND indexproperty(i d, name, 'IsAutoStatisti cs') = 0
AND objectproperty( id, 'IsMsShipped') = 0
ORDER BY "table", name

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Manoj Rajshekar]

Re: information_sch ema permissions

dedejavu@hotmai l.com (Pachydermitis) wrote in message news:<4f954dcf. 0309190902.1d42 1bf4@posting.go ogle.com>...[color=blue]
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCH EMA.CHECK_CONST RAINTS or
> INFORMATION_SCH EMA.KEY_COLUMN_ USAGE
> An sa ID for the master sees everything however.
> Thanks for your help
> Pachydermitis[/color]

Hi,

This would give you the info what you are seeking..

select name, object_name(id) from sysindexes

-Manoj

[Erland Sommarskog]

Re: information_sch ema permissions

Manoj Rajshekar (manrajshekar@y ahoo.com) writes:[color=blue]
> This would give you the info what you are seeking..
>
> select name, object_name(id) from sysindexes[/color]

And a lot more he is not interested in. He will also get the names of
heap tables (tables without clustered indexes), hypothetcial indexes,
statistics and location of text data. This SELECT filters this kind
of information:

SELECT "table" = object_name(id) , name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(i d, name, 'IsHypothetical ') = 0
AND indexproperty(i d, name, 'IsStatistics') = 0
AND indexproperty(i d, name, 'IsAutoStatisti cs') = 0
AND objectproperty( id, 'IsMsShipped') = 0
ORDER BY "table", name

and also filters indexes on system tables.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Pachydermitis]

Re: information_sch ema permissions

Thanks Erland,
I have been able to get the TableName and IndexNames (along with a few
I don't want _WA_. . .) but I can't seem to get the column names or
get rid of the _WA_ ones.

I was trying to get TableName, IndexName, ColumnName

SELECT obj.[name],ind.[name] FROM sysindexes ind
inner join sysobjects obj on ind.[id]=obj.[id]
ORDER BY obj.[name],ind.indid

Thanks again
Pachydermitis

[Erland Sommarskog]

Re: information_sch ema permissions

Pachydermitis (dedejavu@hotma il.com) writes:[color=blue]
> I have been able to get the TableName and IndexNames (along with a few
> I don't want _WA_. . .) but I can't seem to get the column names or
> get rid of the _WA_ ones.[/color]

Had you used the query I suggested, you would have been relieved from the
_WA "indexes". (Which are statistics and hypothetical indexes.)
[color=blue]
> I was trying to get TableName, IndexName, ColumnName[/color]

Here is a query that gives this. For multi-column indexes you get one
row per index. If you want all columns for an index on one line, you
will have run some iteration.

SELECT "table" = object_name(i.i d), i.name,
isclustered = indexproperty(i .id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i .id, name, 'IsHypothetical ') = 0
AND indexproperty(i .id, name, 'IsStatistics') = 0
AND indexproperty(i .id, name, 'IsAutoStatisti cs') = 0
AND objectproperty( i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustere d" DESC, i.name, ik.keyno


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp