|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
querying the data dictionaryI'm building a tool that will connect to a database (Oracle, SQL Server, DB2) and extract information about the schema objects to an XML file. My problem is that I can't seem to find the equivalent for the following query: SELECT LOWER (uc.index_name) AS index_name, LOWER (uc.table_name) AS table_name, LOWER (uc.column_name) AS column_name, descend, NVL (constraint_type, '-') constraint_type FROM user_ind_columns uc LEFT OUTER JOIN user_constraints ON uc.index_name = constraint_name WHERE constraint_type NOT IN ('P', 'R') OR constraint_type IS NULL ORDER BY uc.table_name, uc.index_name, column_position Note that this query gives one row per column in the index. The closest I've been able to come is this, but the fact that the "keys" column in sysindexes has me confused: select name index_name,object_name(id) table_name , '????' as column_name, indexkey_property(id,2,2,'IsDescending') as descend, case when (status & 2)<>0 then 'U' else '-' end as constraint_type from sysindexes where INDEXPROPERTY ( id , name , 'IsAutoStatistics' ) = 0 order by table_name, index_name Now I know that all the information is available using the sp_helpindex stored proc, but isn't it also available by directly querying the data dictionary? I'd rather not have to process SQL Server differently that the other platforms I support which all seem to be able to give me the required information from a single query. Thanks in advance. Dwayne You should refer to the ANSI standard INFORMATION_SCHEMA views, in your case
SELECT * FROM INFORMATION_SCHEMA.COLUMNS HTH, Jens Suessmeyer. Show quote "Dwayne King" wrote: > Hi there, > > I'm building a tool that will connect to a database (Oracle, SQL Server, DB2) and extract information about the schema objects to an XML file. My problem is that I can't seem to find the equivalent for the following query: > > SELECT LOWER (uc.index_name) AS index_name, LOWER (uc.table_name) AS table_name, > LOWER (uc.column_name) AS column_name, descend, > NVL (constraint_type, '-') constraint_type > FROM user_ind_columns uc LEFT OUTER JOIN user_constraints ON uc.index_name = > constraint_name > WHERE constraint_type NOT IN ('P', 'R') OR constraint_type IS NULL > ORDER BY uc.table_name, uc.index_name, column_position > > Note that this query gives one row per column in the index. > > The closest I've been able to come is this, but the fact that the "keys" column in sysindexes has me confused: > > select name index_name,object_name(id) table_name , > '????' as column_name, > indexkey_property(id,2,2,'IsDescending') as descend, > case when (status & 2)<>0 then 'U' else '-' end as constraint_type > from sysindexes > where INDEXPROPERTY ( id , name , 'IsAutoStatistics' ) = 0 > order by table_name, index_name > > Now I know that all the information is available using the sp_helpindex stored proc, but isn't it also available by directly querying the data dictionary? I'd rather not have to process SQL Server differently that the other platforms I support which all seem to be able to give me the required information from a single query. > > Thanks in advance. > > Dwayne > Hello, Dwayne
Try this: select i.name index_name, object_name(i.id) table_name, c.name as column_name, indexkey_property(i.id,2,2,'IsDescending') as descend, case when (i.status & 2)<>0 then 'U' else '-' end as constraint_type from sysindexes i INNER JOIN sysindexkeys k ON i.id=k.id AND i.indid=k.indid INNER JOIN syscolumns c ON c.id=i.id and c.colid=k.colid where INDEXPROPERTY (i.id , i.name , 'IsAutoStatistics' ) = 0 order by table_name, index_name Razvan A-ha! sysindexkeys was the missing piece of course.
This is the final one that I think I'll be going with: select lower(i.name) index_name, lower(object_name(i.id)) table_name, c.name as column_name, case when indexkey_property(i.id,k.indid,1,'IsDescending') = 0 then 'ASC' else 'DESC' end as descend, case when INDEXPROPERTY(i.id, i.name,'IsUnique') = 1 then 'U' else '-' end as constraint_type from sysindexes i INNER JOIN sysindexkeys k ON i.id=k.id AND i.indid=k.indid INNER JOIN syscolumns c ON c.id=i.id and c.colid=k.colid where INDEXPROPERTY (i.id , i.name , 'IsAutoStatistics' ) = 0 and (i.status & 2048) = 0 and OBJECTPROPERTY(i.id,'IsMSShipped') = 0 order by table_name, index_name, keyno The only thing I'm unsure about is my call to INDEXKEY_PROPERTY and the use of the IsDescending. I'm not sure I understand it thoroughly (as evidenced by my hardcoding "1" for the parameter key_ID) Can this property be different for every column in the index? Thanks to all who contributed. DK Show quote "Razvan Socol" <rso***@gmail.com> wrote in message news:1122559313.305889.48210@o13g2000cwo.googlegroups.com... > Hello, Dwayne > > Try this: > > select i.name index_name, > object_name(i.id) table_name, > c.name as column_name, > indexkey_property(i.id,2,2,'IsDescending') as descend, > case when (i.status & 2)<>0 then 'U' else '-' end as > constraint_type > from sysindexes i INNER JOIN sysindexkeys k ON i.id=k.id AND > i.indid=k.indid > INNER JOIN syscolumns c ON c.id=i.id and c.colid=k.colid > where INDEXPROPERTY (i.id , i.name , 'IsAutoStatistics' ) = 0 > order by table_name, index_name > > Razvan > > Can this property [IsDescending] be different for every column in the index? Yes, for example:CREATE TABLE T (id int primary key, x int, y int) CREATE INDEX I ON T(X ASC, Y DESC) > The only thing I'm unsure about is my call to INDEXKEY_PROPERTY The third parameter of INDEXKEY_PROPERTY should be k.keyno.Razvan You'll need to join the sysindexkeys and syscolumns tables to get the names
of the columns. What exactly is your goal? Give an example of your expected result. ML  |
|||||||||||||||||||||||
Other interesting topics