Author Archives: SQLBILearning
Bulk Report Generation using SSIS and SSRS 2008 R2
TSQL Script to Create dynamic indexs on foreign key into tables available in the database or schema
—————————–Script start————————————————————-
Create Procedure Usp_CreateDynamicIndex_On_ForeignKey (@SchemaName VARCHAR(20))
AS
Begin
SET NOCOUNT,ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
Declare @Loop_count int,
@Loop_start int,
@StrSql nvarchar(2000),
@IndexName Varchar(200),
@tableName Varchar(100),
@ColumnName Varchar(100)
Declare @ForeignKeyDetails as table ( SeqNo int,CONSTRAINT_NAME Varchar(200),TABLE_SCHEMA Varchar(200),
TABLE_NAME Varchar(200),COLUMN_NAME Varchar(200),
REFERENCED_TABLE_NAME Varchar(200),REFERENCED_COLUMN_NAME Varchar(200)
)
Begin Try
INSERT INTO @ForeignKeyDetails
SELECT
ROW_NUMBER()over (order by f.parent_object_id) AS SeqNo,
f.name AS CONSTRAINT_NAME,
SCHEMA_NAME(f.SCHEMA_ID)AS TABLE_SCHEMA,
OBJECT_NAME(f.parent_object_id) AS TABLE_NAME,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS COLUMN_NAME,
OBJECT_NAME (f.referenced_object_id) AS REFERENCED_TABLE_NAME,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS REFERENCED_TABLE_NAME
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
LEFT JOIN sys.index_columns ic on fc.parent_object_id = ic.object_id and ic.column_id = fc.parent_column_id
Where ic.index_id IS NULL
AND SCHEMA_NAME(f.SCHEMA_ID) = @SchemaName
Select @Loop_count = Count(*) From @ForeignKeyDetails
Set @Loop_start = 1
While (@Loop_start < = @Loop_count)
Begin
Select @IndexName = ‘IX_Dynamic_’+ COLUMN_NAME,
@tableName = TABLE_NAME,
@ColumnName = COLUMN_NAME
From @ForeignKeyDetails Where SeqNo = @Loop_start
Set @StrSql = ‘CREATE NONCLUSTERED INDEX ‘ + @IndexName + ‘ ON [‘+ @SchemaName + ‘].[‘ + @tableName + ‘]([‘+ @ColumnName + ‘] ASC)’
–Print @strSql
Exec sp_Executesql
@statement = @StrSql
Set @Loop_start = @Loop_start + 1
End
End Try
Begin Catch
Select ERROR_MESSAGE()
End Catch
SET NOCOUNT OFF
End
————————————-Script end here ——————————————————————-
SQLBILearning 