SQL Server - indexes created or not
Asked By hiral jani
03-Feb-12 07:38 AM
Hello,
I want to find the indexes created on all the tables in my database in sql server management studio
Kindly give some suggestion along with some code/ link
D Company replied to hiral jani
Hello Friend.
I know there is one built in Stored Procedure "sp_helpindex" in Sql Server which can display name of Indexes and the columns on which indexes are created. it may serve your purpose.
Hope it helps
Regards
D
kalpana aparnathi replied to hiral jani
hi,
Use below query:
select 'CREATE INDEX idx_' || tablename || '_geom ON ' || fullpath || '
USING GIST ( the_geom ) TABLESPACE optspace ;'
FROM (select tablename, schemaname || '.' || tablename as fullpath
FROM pg_catalog.pg_tables where schemaname not in
('pg_catalog','information_schema')) as tablelist;
Irbaz Hashmi replied to hiral jani
I use this query to get all the indexes
select
t.name, ind.name, col.name,ind.is_unique_constraint,ind.type_desc
,ic.is_included_column
from sys.indexes ind
inner join sys.index_columns ic on ind.object_id = ic.object_id and
ind.index_id = ic.index_id
inner join sys.columns col on ic.object_id = col.object_id and ic.column_id
= col.column_id
inner join sys.tables t on
ind.object_id = t.object_id
order by t.name, ind.name, ind.type_desc,ind.is_unique_constraint
|
If you want some more specific fields you can add them in select clause or you can also add where clause. I am sure it will get you a good start to work on.
server SQL Server Do I have to set up a sql server database on a sql server? Or can I do this on any server SQL Server Setup Discussions SQL Server (1) CREATE DATABASE (1) Databases (1) Database (1) Create
Attach SQL Server 2008 database to SQL Server 2000 Server SQL Server Hi, I have created a database in SQL Server 2008, with compatibility level of SQL Server 2000. Now when I detach the DB
Linked server SQL Server HI its possible create a local sql server as a linked server In sql server 2000 and Sql server 2005 I thought sql server 2005 its not possible thanks
MSDE on Windows 2003 R2 box, new DL385G6 - Install Fails during SQL Services SQL Server I have been finding that I am having trouble with the Crystal Reports Server XI installation failing when it is dealing with SQL. So, as a thought and in case there was something wrong with my SQL portion of the isntall. I thought ok, I will try installing the actual MSDE application direct from Microsoft. So, I downloaded the MSDE for SQL 2000 (which is msde2000a.exe), set my switches and off to the races. It quit seconds left to the installation and bombed with the same errors as the Crystal Reports Server install. The error is the same whether I try to install MSDE by itself or
Backup on Sql server A and restore to Sql server B SQL Server Hi! I have two SQL Server 2008 Std (Sql Server A and Sql Server B) on Windows Server 2008 R2. SQL