Finally I have created one script and it is working for me.
DROP TABLE #temp
DROP TABLE #temp1
DROP TABLE #temp2
--CURSOR TO FIND ALL INDEX'S IN ALL TABLES.
DECLARE @TabName varchar(100)
CREATE TABLE #temp ([Sl_No] [int] IDENTITY(1,1) NOT NULL,TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize int)
DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sysobjects WHERE xtype = 'U' order by name
OPEN cur
FETCH NEXT FROM cur INTO @TabName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp (IndexName, IndexDescr, IndexKeys)
EXEC sp_helpindex @TabName
UPDATE #temp SET TabName = @TabName WHERE TabName IS NULL
FETCH NEXT FROM cur INTO @TabName
END
CLOSE cur
DEALLOCATE cur
DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'
UPDATE #temp SET IndexSize =((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
INNER JOIN #temp T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name
GO
--CURSOR TO FIND ALL INDEXED COLUMNS WHIC IS HAVING NULL VALUES.
DECLARE @TabName1varchar(200)
DECLARE @IndexNamevarchar(200)
DECLARE @IndexDescr varchar(200)
DECLARE @IndexKeysvarchar(200)
DECLARE @IndexSizeint
DECLARE @Row_CountNUMERIC(18,0)
DECLARE @sSQLNVARCHAR(MAX)
DECLARE @Sl_NoNUMERIC(18,0)
DECLARE @identityNUMERIC(18,0)
DECLARE@IntRowCount NUMERIC(18,0)
DECLARE@sql nvarchar(4000)
SET @IntRowCount = 0
CREATE TABLE #temp1 ([Sl_No] [int] IDENTITY(1,1) NOT NULL,TabName varchar(200), IndexName varchar(200),
IndexDescr varchar(200), IndexKeys varchar(200),
IndexSize int,Row_Count NUMERIC(18,0))
CREATE TABLE #temp2 ([Sl_No] [int] IDENTITY(1,1) NOT NULL,TabName varchar(200), IndexName varchar(200),
IndexDescr varchar(200), IndexKeys varchar(200),
IndexSize int,Row_Count NUMERIC(18,0))
DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
SELECTTabName,IndexKeys,IndexName,IndexDescr,IndexSize,Sl_No
FROM#temp
ORDERBY Sl_No
OPEN cur
FETCH
NEXTFROM cur
INTO@TabName1,
@IndexKeys,
@IndexName,
@IndexDescr,
@IndexSize,
@Sl_No
WHILE @@FETCH_STATUS = 0
BEGIN
IF (CHARINDEX(',',@IndexKeys) > 0)
BEGIN
INSERT INTO #temp2(TabName,IndexName,IndexDescr,IndexKeys,IndexSize)
VALUES (@TabName1,@IndexName,@IndexDescr,@IndexKeys,@IndexSize)
END
ELSE
BEGIN
SELECT @sql = N'select @Row_Count = count(*) FROM ' + @TabName1 +
N' WHERE '+@IndexKeys+' is null'
EXEC sp_executesql @sql, N'@Row_Count NUMERIC(18,0) OUTPUT,@IndexKeys varchar(200)', @Row_Count OUTPUT, @IndexKeys
SET @Row_Count = ISNULL(@Row_Count,0)
IF @Row_Count > 0
BEGIN
INSERT INTO #temp1(TabName,IndexName,IndexDescr,IndexKeys,IndexSize,Row_Count)
VALUES (@TabName1,@IndexName,@IndexDescr,@IndexKeys,@IndexSize,@Row_Count)
END
END
FETCH
NEXTFROM cur
INTO@TabName1,
@IndexKeys,
@IndexName,
@IndexDescr,
@IndexSize,
@Sl_No
END
CLOSE cur
DEALLOCATE cur
SELECT Sl_No,TabName,IndexKeys,IndexName,IndexDescr,Row_Count FROM #temp1 ORDER BY TabName,Row_Count
Regards,
BSL