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 5 6929
[posted and mailed, please reply in news]
Pachydermitis (de******@hotma il.com) writes: 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.
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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp de******@hotmai l.com (Pachydermitis) wrote in message news:<4f******* *************** ****@posting.go ogle.com>... 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
Hi,
This would give you the info what you are seeking..
select name, object_name(id) from sysindexes
-Manoj
Manoj Rajshekar (ma**********@y ahoo.com) writes: This would give you the info what you are seeking..
select name, object_name(id) from sysindexes
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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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
Pachydermitis (de******@hotma il.com) writes: 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.
Had you used the query I suggested, you would have been relieved from the
_WA "indexes". (Which are statistics and hypothetical indexes.)
I was trying to get TableName, IndexName, ColumnName
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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tina Harris |
last post by:
I ran the following query in Query Analyzer for a 7 column table.
SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BY
c.colid
The results were:
I_CSD 1
X_STE_XML 2
I_USR_LCK 4
T_CRT_RCD 5
|
by: Dave Sisk |
last post by:
Hi Folks:
I'm a little new to SQLServer, so please pardon my ignorance!
I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
TABLE_CONSTRAINTS. I'm looking for the views that will give me the list of
columns by constraint.
For instance, if Table1 has a unique key called Table1_UK01, I can find that
under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know the
|
by: Brad H McCollum |
last post by:
I'm writing an application using VB 6.0 as the front-end GUI, and the
MSDE version of SQL Server as the back-end (it's a program for a
really small # of users --- less then 3-4).
I'm trying to determine, through the Visual Basic interface, the
permissions of each user that's using the application on his/her
machine.
For example, let's say I'm user "Michael" that's sitting down at my
machine using the app. I've written. The security...
|
by: Mark Johnson |
last post by:
Is it possible that "information_schema.schemata" does not allways work
correctly in MSDE Sql-Server.
I have built an Explorer that will find the Servers (MSDE and SQL-Server
2000) and then should list the Databases found.
For the SQL-Server the results are always correct but not for MSDE.
Mostly Databases are missing allthough the results are often diffrent.
Selden are all of them there, mostly my two Database are effected in some...
|
by: HSalim |
last post by:
Hello:
Does DB2 support the information_schema views into meta data? if not, what
is the equivalent?
Thanks
HS
| |
by: mike.griffin |
last post by:
This is part of the Columns View, if you add a numeric field to your table
and don't provide any Length or Precision then :
numeric_precision is returned as 65535
numeric_scale is returned as 65531
Is this what you'd expect, and what does it mean to create a column with
no Length or Precision, I'm using pgAdmin to create the tables and
columns, but the tables are created and seem to work.
|
by: BB |
last post by:
Hi There,
just moved over from SQL Server to mySQL and finding my way about. In
SQL Server there was a way of retrieving (and setting) metaproperties
for columns and tables. The closest I see to this is the
TABLES.table_comment COLUMNS.column_comment fields in the
INFORMATION_SCHEMA 'Database'.
How do you set these values? Seems like you cannot amend values within
these tables. I am using version 5.0.27.
|
by: Shrek |
last post by:
if I select character_maximum_length from INFORMATION_SCHEMA.COLUMNS
if returns -1 on columns that are defined as varchar(max)
is this normal???
|
by: amit2781 |
last post by:
Hi,
I have created 4 tables in 'amit' database and then I deleted them. Still I able to get information about the table_schema for the table deleted.
After drop table when I fire a query for table_schema from information_schema.tables it will give me result as 'amit'. I didn't understand why it is happnes.
See the below steps done.
mysql> use amit;
mysql> show tables;
+----------------+
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |