Query to generate create index statement for all the indexes in a perticular database. Here it will find only those index which start with 'IX_'

SELECT 'CREATE NONCLUSTERED INDEX [' + si.name + '] ON ' + so.name + '( [' + sc.name + '] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]' test
FROM sysobjects so, syscolumns sc , sysindexkeys sik , sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
AND si.name like 'IX_%'
ORDER BY si.name

To create index statement with "IF NOT EXIST" check

SELECT 'IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = ''' + si.name + ''')
BEGIN CREATE NONCLUSTERED INDEX [' + si.name + '] ON ' + so.name + '( [' + sc.name + '] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END' test
FROM sysobjects so, syscolumns sc , sysindexkeys sik , sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
AND si.name like 'IX_%'
ORDER BY si.name

Query to fetch all duplicate indexes in current database

SELECT si.name
FROM sysobjects so, syscolumns sc, sysindexkeys sik, sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
GROUP BY si.name
HAVING COUNT(DISTINCT(si.id)) > 1
ORDER BY si.name

Query to fetch all indexes on all user defined tables in current database

SELECT so.name TbName, sc.name ColName, si.name IxName
FROM sysobjects so, syscolumns sc, sysindexkeys sik, sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
ORDER BY TbName, ColName

Query to fetch all user defined tables in current database along with it's column name, datatype, length and collation

SELECT ts.TABLE_NAME, cs.COLUMN_NAME, cs.DATA_TYPE,
cs.CHARACTER_MAXIMUM_LENGTH, cs.COLLATION_NAME
FROM INFORMATION_SCHEMA.TABLES ts, INFORMATION_SCHEMA.COLUMNS cs
WHERE ts.TABLE_NAME != 'sysdiagrams'
AND cs.TABLE_NAME = ts.TABLE_NAME
AND cs.COLLATION_NAME IS NOT NULL
ORDER BY ts.TABLE_NAME, cs.COLUMN_NAME

Query to fetch all user defined tables in current database along with total number of rows in it

SELECT so.name, MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U'
AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

Query to fetch all user defined tables in current database

1 - SELECT * FROM sys.tables
2 - SELECT table_name FROM INFORMATION_SCHEMA.TABLES

Query to check wheather tamporary table exist or not?

IF
object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP
TABLE #MyTempTable
END