Mateen
asked on
Index Created Status
SQL2K:
Please have a look at this sql
select x.table_catalog,
x.table_name,
x.table_id,
y.column_name
from
(
select a.table_catalog,
a.table_name,
table_id= object_id(a.table_name),
ordinal_position=min(a.ord
from information_schema.columns
join information_schema.tables b on (a.table_name = b.table_name)
where ( b.table_type = 'base table' )
and ( a.data_type = 'datetime' or a.data_type = 'smalldatetime')
and ( a.column_name not in ('create_date','edit_date'
group by a.table_name,a.table_catal
join information_schema.columns
Some of the rows returned by the above sql are
table_catalog table_name table_id column_name
--------------------------
siddiqsons chem_avg_rate_det 696701880 trans_date
siddiqsons export_invoice_mst 669453659 inv_date
siddiqsons finish_production_det 64367644 prod_date
siddiqsons finish_production_mst 144367929 prod_date
siddiqsons freight_proposal 326500442 proposal_date
I want an extra column ( say has_index) in my result set.
If index has been created on the column then has_index = 1 else 0
So the new resultset would look like
table_catalog table_name table_id column_name has_index
--------------------------
siddiqsons chem_avg_rate_det 696701880 trans_date 1
siddiqsons export_invoice_mst 669453659 inv_date 0
siddiqsons finish_production_det 64367644 prod_date 1
siddiqsons finish_production_mst 144367929 prod_date 1
siddiqsons freight_proposal 326500442 proposal_date 0
Please have a look at this sql
select x.table_catalog,
x.table_name,
x.table_id,
y.column_name
from
(
select a.table_catalog,
a.table_name,
table_id= object_id(a.table_name),
ordinal_position=min(a.ord
from information_schema.columns
join information_schema.tables b on (a.table_name = b.table_name)
where ( b.table_type = 'base table' )
and ( a.data_type = 'datetime' or a.data_type = 'smalldatetime')
and ( a.column_name not in ('create_date','edit_date'
group by a.table_name,a.table_catal
join information_schema.columns
Some of the rows returned by the above sql are
table_catalog table_name table_id column_name
--------------------------
siddiqsons chem_avg_rate_det 696701880 trans_date
siddiqsons export_invoice_mst 669453659 inv_date
siddiqsons finish_production_det 64367644 prod_date
siddiqsons finish_production_mst 144367929 prod_date
siddiqsons freight_proposal 326500442 proposal_date
I want an extra column ( say has_index) in my result set.
If index has been created on the column then has_index = 1 else 0
So the new resultset would look like
table_catalog table_name table_id column_name has_index
--------------------------
siddiqsons chem_avg_rate_det 696701880 trans_date 1
siddiqsons export_invoice_mst 669453659 inv_date 0
siddiqsons finish_production_det 64367644 prod_date 1
siddiqsons finish_production_mst 144367929 prod_date 1
siddiqsons freight_proposal 326500442 proposal_date 0
ASKER
Hi julianva :
A table may and may not have indexes.
I want to know if it has been indexed against certain column.
The certain column is y.column_name in my query
A table may and may not have indexes.
I want to know if it has been indexed against certain column.
The certain column is y.column_name in my query
select x.table_catalog,
x.table_name,
x.table_id,
y.column_name,
case when ik.id is null then 0 else 1 end
from
(
select a.table_catalog,
a.table_name,
table_id= object_id(a.table_name),
ordinal_position=min(a.ord
from information_schema.columns
join information_schema.tables b on (a.table_name = b.table_name)
where ( b.table_type = 'base table' )
and ( a.data_type = 'datetime' or a.data_type = 'smalldatetime')
and ( a.column_name not in ('create_date','edit_date'
group by a.table_name,a.table_catal
join information_schema.columns
left outer join sysindexkeys ik on ik.id = object_id(x.table_name) and y.ORDINAL_POSITION = ik.colid
x.table_name,
x.table_id,
y.column_name,
case when ik.id is null then 0 else 1 end
from
(
select a.table_catalog,
a.table_name,
table_id= object_id(a.table_name),
ordinal_position=min(a.ord
from information_schema.columns
join information_schema.tables b on (a.table_name = b.table_name)
where ( b.table_type = 'base table' )
and ( a.data_type = 'datetime' or a.data_type = 'smalldatetime')
and ( a.column_name not in ('create_date','edit_date'
group by a.table_name,a.table_catal
join information_schema.columns
left outer join sysindexkeys ik on ik.id = object_id(x.table_name) and y.ORDINAL_POSITION = ik.colid
Mherchl
Please look at your code - the case part
Please look at your code - the case part
Julianva,
what's wrong with that part?
what's wrong with that part?
ASKER
Hi Mherchl
Here is a schema of a table anyt
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ANYt]')
drop table [dbo].[ANYt]
GO
CREATE TABLE [dbo].[ANYt] (
[SER_NO] [bigint] IDENTITY (1, 1) NOT NULL ,
[TRANS_NO] [int] NOT NULL ,
[COLA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[COLB] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[COLC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[test_date] [smalldatetime] NULL ,
[test_date2] [smalldatetime] NULL ,
[MUF] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[xyz] [numeric](18, 0) NULL ,
[abc] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANYt] WITH NOCHECK ADD
CONSTRAINT [PK_ANY] PRIMARY KEY CLUSTERED
(
[SER_NO]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANYt] WITH NOCHECK ADD
CONSTRAINT [DF_ANY_CREATE_DATE] DEFAULT (getdate()) FOR [test_date],
CONSTRAINT [DF_ANY_EDIT_DATE] DEFAULT (getdate()) FOR [test_date2],
CONSTRAINT [DF_ANYt_MUF_1] DEFAULT ('N') FOR [MUF]
GO
Clearly the table has pk on column ser_no ( not our column)
and column test_date has no index ( our column)
please note test_date2 is not our column [as it is not min(ordinal_position)]
your query reports 1 againts test_date column. It should have been 0
Here is a schema of a table anyt
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ANYt]')
drop table [dbo].[ANYt]
GO
CREATE TABLE [dbo].[ANYt] (
[SER_NO] [bigint] IDENTITY (1, 1) NOT NULL ,
[TRANS_NO] [int] NOT NULL ,
[COLA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[COLB] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[COLC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[test_date] [smalldatetime] NULL ,
[test_date2] [smalldatetime] NULL ,
[MUF] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[xyz] [numeric](18, 0) NULL ,
[abc] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANYt] WITH NOCHECK ADD
CONSTRAINT [PK_ANY] PRIMARY KEY CLUSTERED
(
[SER_NO]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANYt] WITH NOCHECK ADD
CONSTRAINT [DF_ANY_CREATE_DATE] DEFAULT (getdate()) FOR [test_date],
CONSTRAINT [DF_ANY_EDIT_DATE] DEFAULT (getdate()) FOR [test_date2],
CONSTRAINT [DF_ANYt_MUF_1] DEFAULT ('N') FOR [MUF]
GO
Clearly the table has pk on column ser_no ( not our column)
and column test_date has no index ( our column)
please note test_date2 is not our column [as it is not min(ordinal_position)]
your query reports 1 againts test_date column. It should have been 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi mherchl:
There were not one but many tables which were showing wrong value 1 instead of 0.
I took schema ( full with indexes and triggers etc) of six tables from a database siddiqsons
and created the same in a database test and ran the query on that db. This time the Has_Index value was correct for all copied tables. But the same is wrong when run in db siddiqsons.
For table anyt, I dropped the table and recreated it. Again it was correct.
What is wrong in my original dbs. It must needs maintenance like dbcc dbreindex or whatever I don't know. Can u help.
Note: To check if any column in my table has index or not I do this.
In enterprise manager I open the table in design mode. Click the button Manage Indexes/Kesy and there physically see.
There were not one but many tables which were showing wrong value 1 instead of 0.
I took schema ( full with indexes and triggers etc) of six tables from a database siddiqsons
and created the same in a database test and ran the query on that db. This time the Has_Index value was correct for all copied tables. But the same is wrong when run in db siddiqsons.
For table anyt, I dropped the table and recreated it. Again it was correct.
What is wrong in my original dbs. It must needs maintenance like dbcc dbreindex or whatever I don't know. Can u help.
Note: To check if any column in my table has index or not I do this.
In enterprise manager I open the table in design mode. Click the button Manage Indexes/Kesy and there physically see.
ASKER
Hi mherchl:
a table 'chem_ito_mst' was showing wrong value.
I tried
select table_name = object_name(id),
column_name = col_name(id,colid),indid,k
from sysindexkeys
where object_name(id) = 'chem_ito_mst'
to have this result
table_name column_name indid keyno
chem_ito_mst ito_no 1 1
chem_ito_mst unit_code 2 1
chem_ito_mst transfer_to_unit 3 1
chem_ito_mst trans_date 4 1 --- i am sure this has no index
chem_ito_mst company_code 5 1
chem_ito_mst ito_type 6 1
chem_ito_mst remarks 7 1
chem_ito_mst post_tag 8 1
chem_ito_mst user_name 9 1
chem_ito_mst create_date 10 1
chem_ito_mst edit_date 11 1
This is showing all columns present in the table chem_ito_mst.
So, left outer join will not have null rows.
I see in EM that column trans_date has not got index, but the query says otherwise.
Mherchl, please believe me that some tricky thing is missing somewhere.
I have looked sysindexes, sysindexkey, syscolumns and sysobjects with many
angles but could not find the missing point.
a table 'chem_ito_mst' was showing wrong value.
I tried
select table_name = object_name(id),
column_name = col_name(id,colid),indid,k
from sysindexkeys
where object_name(id) = 'chem_ito_mst'
to have this result
table_name column_name indid keyno
chem_ito_mst ito_no 1 1
chem_ito_mst unit_code 2 1
chem_ito_mst transfer_to_unit 3 1
chem_ito_mst trans_date 4 1 --- i am sure this has no index
chem_ito_mst company_code 5 1
chem_ito_mst ito_type 6 1
chem_ito_mst remarks 7 1
chem_ito_mst post_tag 8 1
chem_ito_mst user_name 9 1
chem_ito_mst create_date 10 1
chem_ito_mst edit_date 11 1
This is showing all columns present in the table chem_ito_mst.
So, left outer join will not have null rows.
I see in EM that column trans_date has not got index, but the query says otherwise.
Mherchl, please believe me that some tricky thing is missing somewhere.
I have looked sysindexes, sysindexkey, syscolumns and sysobjects with many
angles but could not find the missing point.
ASKER
Hi Mherchl
I have found the problematic area, but don't know what to do.
select table_name = object_name(id),
column_name = col_name(id,indid),
Index_name = name from sysindexes
where object_name(id) = 'chem_ito_mst'
table_name column_name index_name
chem_ito_mst ito_no pk_ito_no_mst
chem_ito_mst ito_type _WA_Sys_unit_code_1FA46B10
chem_ito_mst unit_code _WA_Sys_transfer_to_unit_1
chem_ito_mst transfer_to_unit _WA_Sys_trans_date_1FA46B1
chem_ito_mst remarks _WA_Sys_company_code_1FA46
chem_ito_mst trans_date _WA_Sys_ito_type_1FA46B10
chem_ito_mst post_tag _WA_Sys_remarks_1FA46B10
chem_ito_mst company_code _WA_Sys_post_tag_1FA46B10
chem_ito_mst user_name _WA_Sys_user_name_1FA46B10
chem_ito_mst create_date _WA_Sys_create_date_1FA46B
chem_ito_mst edit_date _WA_Sys_edit_date_1FA46B10
Now
chem_ito_mst trans_date _WA_Sys_ito_type_1FA46B10
Because of this sysindexkeys will also hold trans_date for this table chem_ito_mst
I have not created index on trans_date. EM Index area confirms that, then
_WA_Sys_ito_type_1FA46B10 has come from where. And now, what is the remedy.
I have found the problematic area, but don't know what to do.
select table_name = object_name(id),
column_name = col_name(id,indid),
Index_name = name from sysindexes
where object_name(id) = 'chem_ito_mst'
table_name column_name index_name
chem_ito_mst ito_no pk_ito_no_mst
chem_ito_mst ito_type _WA_Sys_unit_code_1FA46B10
chem_ito_mst unit_code _WA_Sys_transfer_to_unit_1
chem_ito_mst transfer_to_unit _WA_Sys_trans_date_1FA46B1
chem_ito_mst remarks _WA_Sys_company_code_1FA46
chem_ito_mst trans_date _WA_Sys_ito_type_1FA46B10
chem_ito_mst post_tag _WA_Sys_remarks_1FA46B10
chem_ito_mst company_code _WA_Sys_post_tag_1FA46B10
chem_ito_mst user_name _WA_Sys_user_name_1FA46B10
chem_ito_mst create_date _WA_Sys_create_date_1FA46B
chem_ito_mst edit_date _WA_Sys_edit_date_1FA46B10
Now
chem_ito_mst trans_date _WA_Sys_ito_type_1FA46B10
Because of this sysindexkeys will also hold trans_date for this table chem_ito_mst
I have not created index on trans_date. EM Index area confirms that, then
_WA_Sys_ito_type_1FA46B10 has come from where. And now, what is the remedy.
ASKER
Hi mherchl
At last got it.
select x.table_catalog,
x.table_name,
y.column_name,
ik.Index_Name,
Row = ( select max(rows) from sysindexes a where object_name(a.id) = x.table_name and a.indid < 2)
from
(
select a.table_catalog,
a.table_name,
table_id= object_id(a.table_name),
ordinal_position=min(a.ord
from information_schema.columns
join information_schema.tables b on (a.table_name = b.table_name)
where ( b.table_type = 'base table' )
and ( a.data_type = 'datetime' or a.data_type = 'smalldatetime')
and ( a.column_name not in ('create_date','edit_date'
and ( a.table_name not like '%log%')
and ( a.table_name not like 'del%')
and ( a.table_name not like '%_det%')
group by a.table_name,a.table_catal
join information_schema.columns
left outer join
(
select table_name=o.name,
column_name=col_name(k.id,
index_name=i.name
from sysobjects o
join sysindexes i on (o.id=i.id)
join sysindexkeys k on (i.id=k.id and i.indid=k.indid)
where o.xtype='u'
and i.status&8388608 = 0
) ik on (x.table_name=ik.table_nam
order by index_name,row desc
To produce , what I want, I must write
status&8388608 = 0
Now, I intend to create index on key date column on table where it has more than 2000 rows.
Because most of the time the table is quereid for a particular period of dates.
Will I do good? Your opinion will be highly appreciated.
Thanks in advance.
At last got it.
select x.table_catalog,
x.table_name,
y.column_name,
ik.Index_Name,
Row = ( select max(rows) from sysindexes a where object_name(a.id) = x.table_name and a.indid < 2)
from
(
select a.table_catalog,
a.table_name,
table_id= object_id(a.table_name),
ordinal_position=min(a.ord
from information_schema.columns
join information_schema.tables b on (a.table_name = b.table_name)
where ( b.table_type = 'base table' )
and ( a.data_type = 'datetime' or a.data_type = 'smalldatetime')
and ( a.column_name not in ('create_date','edit_date'
and ( a.table_name not like '%log%')
and ( a.table_name not like 'del%')
and ( a.table_name not like '%_det%')
group by a.table_name,a.table_catal
join information_schema.columns
left outer join
(
select table_name=o.name,
column_name=col_name(k.id,
index_name=i.name
from sysobjects o
join sysindexes i on (o.id=i.id)
join sysindexkeys k on (i.id=k.id and i.indid=k.indid)
where o.xtype='u'
and i.status&8388608 = 0
) ik on (x.table_name=ik.table_nam
order by index_name,row desc
To produce , what I want, I must write
status&8388608 = 0
Now, I intend to create index on key date column on table where it has more than 2000 rows.
Because most of the time the table is quereid for a particular period of dates.
Will I do good? Your opinion will be highly appreciated.
Thanks in advance.
isindexed(tablename)
( object_id('tablename'),'IS