Script out all SQL Server Indexes in a Database using T-SQL (2022)

I have updated my previously posted query to include more index types and in doing so, split the query into two (one for SQL Server 2012 and earlier and one for SQL Server 2014 and later). Again, thank you for writing this post!

SQL Server 2014 and later:

/*

This script will generate the SQL to create all indexes in the database of SQL Server 2014 and greater.

Copied from https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/.

Modified by GaryS 4-14-2016.

Modified to add ALTER TABLE for PK indexes and unique constraints, data compression, filtered indexes, XML indexes,

partitioned indexes, spatial indexes, indexes on memory-optimized tables (including hash indexes), and online options (when applicable).

NOTE: Does not script out Extended Properties (comments), or full-text indexes (and catalogs).

*/

DECLARE @SchemaName varchar(100);

DECLARE @TableName varchar(256);

DECLARE @IndexName varchar(256);

DECLARE @ColumnName varchar(100);

DECLARE @is_unique varchar(100);

DECLARE @IndexTypeDesc varchar(100);

DECLARE @FileGroupName varchar(100);

DECLARE @is_disabled varchar(100);

DECLARE @IndexOptions varchar(MAX);

DECLARE @DataCompressionType varchar(100);

DECLARE @is_filtered bit;

DECLARE @filter_definition varchar(MAX);

DECLARE @is_primary_key bit;

DECLARE @is_unique_constraint bit;

DECLARE @IndexColumnId int;

DECLARE @IsDescendingKey int;

DECLARE @IsIncludedColumn int;

DECLARE @compression_delay int;

DECLARE @TSQLScripCreationIndex varchar(MAX);

DECLARE @TSQLScripDisableIndex varchar(MAX);

DECLARE CursorIndex

CURSOR

FOR

--CTE to collect partitioned index information

WITH PartitionedIndexes AS (

SELECT t.object_id Object_ID,

t.name TableName,

ic.column_id PartitioningColumnID,

c.name PartitioningColumnName,

s.name AS [partition_scheme],

ix.name AS IndexName,

ix.index_id

FROM sys.tables t

INNER JOIN sys.indexes i ON i.object_id = t.object_id

INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id

AND ic.object_id = t.object_id

INNER JOIN sys.columns c ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

INNER JOIN sys.indexes ix ON t.object_id=ix.object_id

AND ix.index_id = i.index_id

WHERE ic.partition_ordinal = 1

)

SELECT schema_name(t.schema_id) [schema_name],

t.name TableName,

ix.name IndexName,

CASE

WHEN ix.is_unique = 1 THEN 'UNIQUE '

ELSE ''

END IsUnique,

CASE

WHEN t.object_id IN (SELECT OBJECT_ID FROM sys.tables WHERE is_memory_optimized = 1)

AND ix.type_desc <> 'NONCLUSTERED HASH'

THEN 'MEMORY_OPTIMIZED'

ELSE ix.type_desc

END AS

type_desc,

CASE

WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, '

ELSE 'PAD_INDEX = OFF, '

END +

CASE

WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, '

ELSE 'STATISTICS_NORECOMPUTE = OFF, '

END +

CASE

WHEN (ix.is_primary_key = 1 OR ix.is_unique_constraint = 1) THEN ''

ELSE 'SORT_IN_TEMPDB = OFF, '

END +

CASE

WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, '

ELSE 'IGNORE_DUP_KEY = OFF, '

END +

CASE

WHEN (@@Version LIKE '%ENTERPRISE%' AND (LOBTable.CanBeBuiltOnline IS NULL AND ix.type_desc = 'CLUSTERED')

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0)

OR (@@Version LIKE '%ENTERPRISE%' AND (LOBIndexes.CanBeBuiltOnline IS NULL AND ix.type_desc <> 'CLUSTERED')

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0)

THEN 'ONLINE = ON, '

ELSE ''

END +

CASE

WHEN ix.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, '

ELSE 'ALLOW_ROW_LOCKS = OFF, '

END +

CASE

WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, '

ELSE 'ALLOW_PAGE_LOCKS = OFF, '

END +

CASE

WHEN CAST(ix.fill_factor AS VARCHAR(3)) = 0 THEN ''

ELSE 'FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) + ', '

END +

CASE

WHEN p.data_compression_desc IS NULL THEN 'DATA_COMPRESSION = NONE'

ELSE 'DATA_COMPRESSION = ' + p.data_compression_desc

END

AS IndexOptions,

ix.is_disabled,

CASE

WHEN ic.IsColumnPartitioned = 1 THEN '[' + PIdx.[partition_scheme] + ']' + '(' + '[' + PIdx.PartitioningColumnName + ']' + ')'

WHEN ic.IsColumnPartitioned = 0 THEN '[' + FILEGROUP_NAME(ix.data_space_id) + ']'

END AS FileGroupName,

ix.has_filter,

ix.filter_definition,

ix.is_primary_key,

ix.is_unique_constraint,

ix.compression_delay

FROM sys.tables t

INNER JOIN sys.indexes ix ON t.object_id = ix.object_id

INNER JOIN

(SELECT DISTINCT OBJECT_ID,

index_id,

MAX(partition_ordinal) AS IsColumnPartitioned

FROM sys.index_columns

GROUP BY OBJECT_ID,

index_id) ic ON ic.index_id = ix.index_id

AND ic.object_id = t.object_id

LEFT JOIN

(SELECT DISTINCT object_id,

index_id,

data_compression_desc

FROM sys.partitions) p ON ix.object_id = p.object_id

AND ix.index_id = p.index_id

LEFT JOIN PartitionedIndexes PIdx ON PIdx.Object_ID = t.object_id

AND PIdx.index_id = ix.index_id

LEFT JOIN

(SELECT DISTINCT object_id,

0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE t.Name IN ('image',

'ntext',

'text',

'XML')

OR (t.Name IN ('varchar',

'nvarchar',

'varbinary')

AND c.max_length = -1)

OR c.is_filestream = 1) LOBTable ON LOBTable.object_id = t.object_id

LEFT JOIN

(SELECT DISTINCT c.object_id,

i.index_id,

0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

(Video) TSQL: Rebuild All Indexes In A Database

INNER JOIN sys.indexes i ON ic.object_id = i.object_id

AND ic.index_id = i.index_id

WHERE t.Name IN ('image',

'ntext',

'text',

'XML')

OR (t.Name IN ('varchar',

'nvarchar',

'varbinary')

AND c.max_length = -1)

OR c.is_filestream = 1) LOBIndexes ON LOBIndexes.object_id = t.object_id

AND LOBIndexes.index_id = ix.index_id

WHERE

/*****************Ignores PK indexes************************/

--ix.type > 0 AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0 AND

/***********************************************************/

t.is_ms_shipped = 0

AND t.name <> 'sysdiagrams'

AND ix.name IS NOT NULL

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

/***********************************************************/

ORDER BY schema_name(t.schema_id),

t.name,

ix.name

OPEN CursorIndex

FETCH NEXT

FROM CursorIndex

INTO @SchemaName,

@TableName,

@IndexName,

@is_unique,

@IndexTypeDesc,

@IndexOptions,

@is_disabled,

@FileGroupName,

@is_filtered,

@filter_definition,

@is_primary_key,

@is_unique_constraint,

@compression_delay

WHILE (@@fetch_status = 0)

BEGIN

DECLARE @IndexColumns varchar(MAX);

DECLARE @IncludedColumns varchar(MAX);

SET @IndexColumns='';

SET @IncludedColumns='';

SET @DataCompressionType = '';

DECLARE CursorIndexColumn

CURSOR

FOR

SELECT col.name,

ixc.is_descending_key,

ixc.is_included_column

FROM sys.tables tb

INNER JOIN sys.indexes ix ON tb.object_id = ix.object_id

INNER JOIN sys.index_columns ixc ON ix.object_id = ixc.object_id

AND ix.index_id = ixc.index_id

INNER JOIN sys.columns col ON ixc.object_id = col.object_id

AND ixc.column_id = col.column_id

WHERE

/*****************Ignores PK indexes************************/

--ix.type > 0 and (ix.is_primary_key = 0 or ix.is_unique_constraint = 0) AND

/***********************************************************/

schema_name(tb.schema_id) = @SchemaName

AND tb.name = @TableName

AND ix.name = @IndexName

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

/***********************************************************/

ORDER BY ixc.key_ordinal

OPEN CursorIndexColumn

FETCH NEXT

FROM CursorIndexColumn

INTO @ColumnName,

@IsDescendingKey,

@IsIncludedColumn

WHILE (@@fetch_status=0)

BEGIN

IF @IsIncludedColumn = 0

OR @IndexTypeDesc LIKE '%COLUMNSTORE%' OR @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial'

OR @IndexTypeDesc = 'NONCLUSTERED HASH'

SET @IndexColumns =

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%'

THEN @IndexColumns + '[' + @ColumnName + '], '

WHEN @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial' OR @IndexTypeDesc = 'NONCLUSTERED HASH'

THEN @IndexColumns + '[' + @ColumnName + '], '

ELSE @IndexColumns + '[' + @ColumnName + ']' +

CASE

WHEN @IsDescendingKey = 1 THEN ' DESC, '

ELSE ' ASC, '

END

END

ELSE

SET @IncludedColumns = @IncludedColumns + '[' + @ColumnName + '], '

FETCH NEXT

FROM CursorIndexColumn

INTO @ColumnName,

@IsDescendingKey,

@IsIncludedColumn

END

CLOSE CursorIndexColumn

DEALLOCATE CursorIndexColumn

SET @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1);

SET @IncludedColumns =

CASE

WHEN len(@IncludedColumns) > 0 THEN substring(@IncludedColumns, 1, len(@IncludedColumns)-1)

ELSE ''

END;

SET @TSQLScripCreationIndex = '';

SET @TSQLScripDisableIndex = '';

SET @DataCompressionType = SUBSTRING(@IndexOptions,CHARINDEX('DATA_COMPRESSION',@IndexOptions)+18,LEN(@IndexOptions));

SET @TSQLScripCreationIndex =

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' THEN

CASE

WHEN @IndexTypeDesc LIKE 'CLUSTERED%' THEN

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) +

'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType)) + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';'

WHEN @is_filtered = 1 THEN

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' + + CHAR(13) +

'WHERE ' + @filter_definition + CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType)) + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';'

ELSE

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' +

CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType)) + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';' END

WHEN @IndexTypeDesc = 'MEMORY_OPTIMIZED' THEN

CASE WHEN @is_primary_key = 1 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + ' PRIMARY KEY NONCLUSTERED'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'

WHEN @is_primary_key = 0 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @is_unique + ' NONCLUSTERED'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'END

WHEN @IndexTypeDesc = 'NONCLUSTERED HASH' THEN

CASE WHEN @is_primary_key = 1 THEN

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY NONCLUSTERED HASH'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name = @IndexName)

WHEN @is_primary_key = 0 THEN

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name = @IndexName) END

WHEN @IndexTypeDesc = 'XML' THEN

(Video) ScriptOut All SQL Agent Jobs - SQL Server.

CASE WHEN EXISTS (SELECT name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML'

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN

'CREATE PRIMARY XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ');'

WHEN EXISTS (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description <> 'PRIMARY_XML'

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN

(SELECT 'CREATE XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ')' + CHAR(13) + 'USING XML INDEX ' + QUOTENAME(P.name)

+ ' FOR ' + I.secondary_type_desc collate latin1_general_cs_as + ' WITH (' + @IndexOptions+ ');'

FROM sys.xml_indexes I

INNER JOIN (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML') P

ON P.object_id = I.object_id AND P.index_id = I.using_xml_index_id AND I.name = @IndexName)

END

WHEN @IndexTypeDesc = 'spatial' THEN

(SELECT 'CREATE SPATIAL INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ')' + 'USING ' + tessellation_scheme + CHAR(13)

+ 'WITH (BOUNDING_BOX =(' + CONVERT(varchar,bounding_box_xmin) + ', ' + CONVERT(varchar,bounding_box_ymin) + ', '

+ CONVERT(varchar,bounding_box_xmax) + ', ' + CONVERT(varchar,bounding_box_ymax)

+ '), GRIDS =(LEVEL1 = ' + level_1_grid_desc + ',LEVEL_2 = ' + level_2_grid_desc + ',LEVEL3 = ' +

level_3_grid_desc + ',LEVEL4 = ' + level_4_grid_desc + '),

CELLS_PER_OBJECT = ' + CONVERT(varchar,cells_per_object) + ', ' + @IndexOptions + ') ON ' + @FileGroupName + ';'

FROM sys.spatial_index_tessellations

WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND index_id =

(SELECT index_id from sys.indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name = @IndexName))

WHEN @is_filtered = 1 THEN

'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) ++ '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' +

CASE

WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

ELSE ''

END +

CHAR(13) + 'WHERE ' + @filter_definition + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

WHEN @is_primary_key = 1 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY ' + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

WHEN @is_unique_constraint = 1 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + @is_unique + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ');'

ELSE

'CREATE '+ @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' +

CASE

WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

ELSE ''

END +

CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

END;

IF @is_disabled = 1

SET @TSQLScripDisableIndex = CHAR(13) +'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) + 'GO' + + CHAR(13)

PRINT @TSQLScripCreationIndex;

PRINT 'GO';

PRINT @TSQLScripDisableIndex;

FETCH NEXT

FROM CursorIndex

INTO @SchemaName,

@TableName,

@IndexName,

@is_unique,

@IndexTypeDesc,

@IndexOptions,

@is_disabled,

@FileGroupName,

@is_filtered,

@filter_definition,

@is_primary_key,

@is_unique_constraint,

@compression_delay

END;

CLOSE CursorIndex;

DEALLOCATE CursorIndex;

SQL Server 2012 and earlier:

/*

This script will generate the SQL to create all indexes in the database.

Copied from https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/.

Modified by GaryS 4-14-2016.

Modified to add ALTER TABLE for PK indexes and unique constraints, data compression, filtered indexes, XML indexes,

partitioned indexes, spatial indexes, and online options(when applicable).

NOTE: Does not script out Extended Properties (comments), or full-text indexes (and catalogs).

Also, due to several columns missing on older versions of SQL Server, I have commented out lines that only apply to

SQL Server 2014 or greater.

*/

DECLARE @SchemaName varchar(100);

DECLARE @TableName varchar(256);

DECLARE @IndexName varchar(256);

DECLARE @ColumnName varchar(100);

DECLARE @is_unique varchar(100);

DECLARE @IndexTypeDesc varchar(100);

DECLARE @FileGroupName varchar(100);

DECLARE @is_disabled varchar(100);

DECLARE @IndexOptions varchar(MAX);

DECLARE @DataCompressionType varchar(100);

DECLARE @is_filtered bit;

DECLARE @filter_definition varchar(MAX);

DECLARE @is_primary_key bit;

DECLARE @is_unique_constraint bit;

DECLARE @IndexColumnId int;

DECLARE @IsDescendingKey int;

DECLARE @IsIncludedColumn int;

DECLARE @compression_delay int;

DECLARE @TSQLScripCreationIndex varchar(MAX);

DECLARE @TSQLScripDisableIndex varchar(MAX);

DECLARE CursorIndex

CURSOR

FOR

--CTE to collect partitioned index information

WITH PartitionedIndexes AS (

SELECT t.object_id Object_ID,

t.name TableName,

ic.column_id PartitioningColumnID,

c.name PartitioningColumnName,

s.name AS [partition_scheme],

ix.name AS IndexName,

ix.index_id

FROM sys.tables t

INNER JOIN sys.indexes i ON i.object_id = t.object_id

INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id

AND ic.object_id = t.object_id

INNER JOIN sys.columns c ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

INNER JOIN sys.indexes ix ON t.object_id=ix.object_id

AND ix.index_id = i.index_id

WHERE ic.partition_ordinal = 1

)

SELECT schema_name(t.schema_id) [schema_name],

t.name TableName,

ix.name IndexName,

CASE

WHEN ix.is_unique = 1 THEN 'UNIQUE '

ELSE ''

END IsUnique,

/*

--Commented out for older versions of SQL Server

CASE

WHEN t.object_id IN (SELECT OBJECT_ID FROM sys.tables WHERE is_memory_optimized = 1)

AND ix.type_desc <> 'NONCLUSTERED HASH'

THEN 'MEMORY_OPTIMIZED'

ELSE*/ ix.type_desc

--END

AS type_desc,

CASE

WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, '

ELSE 'PAD_INDEX = OFF, '

END +

CASE

WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, '

ELSE 'STATISTICS_NORECOMPUTE = OFF, '

END +

CASE

WHEN (ix.is_primary_key = 1 OR ix.is_unique_constraint = 1) THEN ''

ELSE 'SORT_IN_TEMPDB = OFF, '

END +

CASE

WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, '

(Video) Understanding and using indexes on SQL Server

ELSE 'IGNORE_DUP_KEY = OFF, '

END +

CASE

WHEN (@@Version LIKE '%ENTERPRISE%' AND (LOBTable.CanBeBuiltOnline IS NULL AND ix.type_desc = 'CLUSTERED')

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0)

OR (@@Version LIKE '%ENTERPRISE%' AND (LOBIndexes.CanBeBuiltOnline IS NULL AND ix.type_desc <> 'CLUSTERED')

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0)

THEN 'ONLINE = ON, '

ELSE ''

END +

CASE

WHEN ix.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, '

ELSE 'ALLOW_ROW_LOCKS = OFF, '

END +

CASE

WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, '

ELSE 'ALLOW_PAGE_LOCKS = OFF, '

END +

CASE

WHEN CAST(ix.fill_factor AS VARCHAR(3)) = 0 THEN ''

ELSE 'FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) + ', '

END +

CASE

WHEN p.data_compression_desc IS NULL THEN 'DATA_COMPRESSION = NONE'

ELSE 'DATA_COMPRESSION = ' + p.data_compression_desc

END

AS IndexOptions,

ix.is_disabled,

CASE

WHEN ic.IsColumnPartitioned = 1 THEN '[' + PIdx.[partition_scheme] + ']' + '(' + '[' + PIdx.PartitioningColumnName + ']' + ')'

WHEN ic.IsColumnPartitioned = 0 THEN '[' + FILEGROUP_NAME(ix.data_space_id) + ']'

END AS FileGroupName,

ix.has_filter,

ix.filter_definition,

ix.is_primary_key,

ix.is_unique_constraint

/*

--Commented out for older versions of SQL Server

,ix.compression_delay*/

FROM sys.tables t

INNER JOIN sys.indexes ix ON t.object_id = ix.object_id

INNER JOIN

(SELECT DISTINCT OBJECT_ID,

index_id,

MAX(partition_ordinal) AS IsColumnPartitioned

FROM sys.index_columns

GROUP BY OBJECT_ID,

index_id) ic ON ic.index_id = ix.index_id

AND ic.object_id = t.object_id

LEFT JOIN

(SELECT DISTINCT object_id,

index_id,

data_compression_desc

FROM sys.partitions) p ON ix.object_id = p.object_id

AND ix.index_id = p.index_id

LEFT JOIN PartitionedIndexes PIdx ON PIdx.Object_ID = t.object_id

AND PIdx.index_id = ix.index_id

LEFT JOIN

(SELECT DISTINCT object_id,

0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE t.Name IN ('image',

'ntext',

'text',

'XML')

OR (t.Name IN ('varchar',

'nvarchar',

'varbinary')

AND c.max_length = -1)

OR c.is_filestream = 1) LOBTable ON LOBTable.object_id = t.object_id

LEFT JOIN

(SELECT DISTINCT c.object_id,

i.index_id,

0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

INNER JOIN sys.indexes i ON ic.object_id = i.object_id

AND ic.index_id = i.index_id

WHERE t.Name IN ('image',

'ntext',

'text',

'XML')

OR (t.Name IN ('varchar',

'nvarchar',

'varbinary')

AND c.max_length = -1)

OR c.is_filestream = 1) LOBIndexes ON LOBIndexes.object_id = t.object_id

AND LOBIndexes.index_id = ix.index_id

WHERE

/*****************Ignores PK indexes************************/

--ix.type > 0 AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0 AND

/***********************************************************/

t.is_ms_shipped = 0

AND t.name <> 'sysdiagrams'

AND ix.name IS NOT NULL

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

/***********************************************************/

ORDER BY schema_name(t.schema_id),

t.name,

ix.name

OPEN CursorIndex

FETCH NEXT

FROM CursorIndex

INTO @SchemaName,

@TableName,

@IndexName,

@is_unique,

@IndexTypeDesc,

@IndexOptions,

@is_disabled,

@FileGroupName,

@is_filtered,

@filter_definition,

@is_primary_key,

@is_unique_constraint

/*

--Commented out for older versions of SQL Server

,@compression_delay*/

WHILE (@@fetch_status = 0)

BEGIN

DECLARE @IndexColumns varchar(MAX);

DECLARE @IncludedColumns varchar(MAX);

SET @IndexColumns='';

SET @IncludedColumns='';

SET @DataCompressionType = '';

DECLARE CursorIndexColumn

CURSOR

FOR

SELECT col.name,

ixc.is_descending_key,

ixc.is_included_column

FROM sys.tables tb

INNER JOIN sys.indexes ix ON tb.object_id = ix.object_id

INNER JOIN sys.index_columns ixc ON ix.object_id = ixc.object_id

AND ix.index_id = ixc.index_id

INNER JOIN sys.columns col ON ixc.object_id = col.object_id

AND ixc.column_id = col.column_id

WHERE

/*****************Ignores PK indexes************************/

--ix.type > 0 and (ix.is_primary_key = 0 or ix.is_unique_constraint = 0) AND

/***********************************************************/

schema_name(tb.schema_id) = @SchemaName

AND tb.name = @TableName

AND ix.name = @IndexName

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

/***********************************************************/

ORDER BY ixc.key_ordinal

(Video) How do SQL Indexes Work

OPEN CursorIndexColumn

FETCH NEXT

FROM CursorIndexColumn

INTO @ColumnName,

@IsDescendingKey,

@IsIncludedColumn

WHILE (@@fetch_status=0)

BEGIN

IF @IsIncludedColumn = 0

OR @IndexTypeDesc LIKE '%COLUMNSTORE%' OR @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial'

OR @IndexTypeDesc = 'NONCLUSTERED HASH'

SET @IndexColumns =

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%'

THEN @IndexColumns + '[' + @ColumnName + '], '

WHEN @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial' OR @IndexTypeDesc = 'NONCLUSTERED HASH'

THEN @IndexColumns + '[' + @ColumnName + '], '

ELSE @IndexColumns + '[' + @ColumnName + ']' +

CASE

WHEN @IsDescendingKey = 1 THEN ' DESC, '

ELSE ' ASC, '

END

END

ELSE

SET @IncludedColumns = @IncludedColumns + '[' + @ColumnName + '], '

FETCH NEXT

FROM CursorIndexColumn

INTO @ColumnName,

@IsDescendingKey,

@IsIncludedColumn

END

CLOSE CursorIndexColumn

DEALLOCATE CursorIndexColumn

SET @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1);

SET @IncludedColumns =

CASE

WHEN len(@IncludedColumns) > 0 THEN substring(@IncludedColumns, 1, len(@IncludedColumns)-1)

ELSE ''

END;

SET @TSQLScripCreationIndex = '';

SET @TSQLScripDisableIndex = '';

SET @DataCompressionType = SUBSTRING(@IndexOptions,CHARINDEX('DATA_COMPRESSION',@IndexOptions)+18,LEN(@IndexOptions));

SET @TSQLScripCreationIndex =

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' THEN

CASE

WHEN @IndexTypeDesc LIKE 'CLUSTERED%' THEN

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) +

'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType)) + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';'

WHEN @is_filtered = 1 THEN

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' + + CHAR(13) +

'WHERE ' + @filter_definition + CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType)) + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';'

ELSE

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' +

CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType)) + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';' END

/*

--Commented out for older versions of SQL Server

WHEN @IndexTypeDesc = 'MEMORY_OPTIMIZED' THEN

CASE WHEN @is_primary_key = 1 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + ' PRIMARY KEY NONCLUSTERED'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'

WHEN @is_primary_key = 0 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @is_unique + ' NONCLUSTERED'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'END

WHEN @IndexTypeDesc = 'NONCLUSTERED HASH' THEN

CASE WHEN @is_primary_key = 1 THEN

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY NONCLUSTERED HASH'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name = @IndexName)

WHEN @is_primary_key = 0 THEN

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name = @IndexName) END

*/

WHEN @IndexTypeDesc = 'XML' THEN

CASE WHEN EXISTS (SELECT name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML'

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN

'CREATE PRIMARY XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ');'

WHEN EXISTS (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description <> 'PRIMARY_XML'

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN

(SELECT 'CREATE XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ')' + CHAR(13) + 'USING XML INDEX ' + QUOTENAME(P.name)

+ ' FOR ' + I.secondary_type_desc collate latin1_general_cs_as + ' WITH (' + @IndexOptions+ ');'

FROM sys.xml_indexes I

INNER JOIN (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML') P

ON P.object_id = I.object_id AND P.index_id = I.using_xml_index_id AND I.name = @IndexName)

END

WHEN @IndexTypeDesc = 'spatial' THEN

(SELECT 'CREATE SPATIAL INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ')' + 'USING ' + tessellation_scheme + CHAR(13)

+ 'WITH (BOUNDING_BOX =(' + CONVERT(varchar,bounding_box_xmin) + ', ' + CONVERT(varchar,bounding_box_ymin) + ', '

+ CONVERT(varchar,bounding_box_xmax) + ', ' + CONVERT(varchar,bounding_box_ymax)

+ '), GRIDS =(LEVEL1 = ' + level_1_grid_desc + ',LEVEL_2 = ' + level_2_grid_desc + ',LEVEL3 = ' +

level_3_grid_desc + ',LEVEL4 = ' + level_4_grid_desc + '),

CELLS_PER_OBJECT = ' + CONVERT(varchar,cells_per_object) + ', ' + @IndexOptions + ') ON ' + @FileGroupName + ';'

FROM sys.spatial_index_tessellations

WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND index_id =

(SELECT index_id from sys.indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name = @IndexName))

WHEN @is_filtered = 1 THEN

'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) ++ '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' +

CASE

WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

ELSE ''

END +

CHAR(13) + 'WHERE ' + @filter_definition + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

WHEN @is_primary_key = 1 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY ' + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

WHEN @is_unique_constraint = 1 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + @is_unique + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ');'

ELSE

'CREATE '+ @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' +

CASE

WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

ELSE ''

END +

CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

END;

IF @is_disabled = 1

SET @TSQLScripDisableIndex = CHAR(13) +'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' +

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) + 'GO' + + CHAR(13)

PRINT @TSQLScripCreationIndex;

PRINT 'GO';

PRINT @TSQLScripDisableIndex;

FETCH NEXT

FROM CursorIndex

INTO @SchemaName,

@TableName,

@IndexName,

@is_unique,

@IndexTypeDesc,

@IndexOptions,

@is_disabled,

@FileGroupName,

@is_filtered,

@filter_definition,

@is_primary_key,

@is_unique_constraint

/*

--Commented out for older versions of SQL Server

,@compression_delay */

END;

(Video) How to rebuild all indexes of a table SQL server

CLOSE CursorIndex;

DEALLOCATE CursorIndex;

FAQs

How do you script all the indexes in a database in SQL Server? ›

How to Get Table Script with Their all Indexes in SQL Server
  1. Steps: Right click on you database - > Tasks - > Generate Scripts ->
  2. Next - > Next ->
  3. Set Script indexes =true.
  4. Check tables - > next.
  5. Check sales_report table - > next.
Sep 23, 2013

How do I script an entire SQL Server database? ›

Script the whole database
  1. Open the SQL Server Management Studio.
  2. In the Object Explorer, expand Databases, and then locate the database that you want to script.
  3. Right-click the database, point to Tasks, and then select Generate Scripts.
  4. In the script wizard, verify that the correct database is selected.
Aug 18, 2022

What is Tsql scripting? ›

T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables.

How get script of all tables in SQL Server? ›

Generate Database Script in SQL Server

Now right-click the database then Tasks->Generate scripts. After that a window will open. Select the database and always check "script all objects in the selected database". It will generate a script for all the tables, sp, views, functions and anything in that database.

How do I create an index script in SQL Server? ›

This script will generate the SQL to create an index for every foreign key that is missing one. DECLARE @IndexName TABLE ([SQL] NVARCHAR(500)); WITH v_NonIndexedFKColumns AS ( SELECT [Table_Name] = OBJECT_NAME(a. [parent_object_id]), [Column_Name] = b.

How can we get the list of all the indexes on a table? ›

To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.

What is a SQL script example? ›

Intro to SQL Scripts - YouTube

How do I write a SQL database script? ›

To create an SQL script in the Script Editor:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts. The SQL Scripts page appears.
  2. Click the Create button. ...
  3. In Script Name, enter a name for the script. ...
  4. Enter the SQL statements, PL/SQL blocks you want to include in your script. ...
  5. Click Create.

How do I script a SQL database with all tables? ›

1 Answer
  1. Right click Database.
  2. Select All Tasks > Generate SQL Scripts.
  3. Click Show All.
  4. Check All Tables.
  5. Click the Formatting tab. Select the options you require.
  6. Click the Options tab. ...
  7. Then choose whether you want it all in one file or one file per object.
Feb 25, 2011

How do I run a T-SQL script? ›

Run the script file
  1. Open a command prompt window.
  2. In the Command Prompt window, type: sqlcmd -S myServer\instanceName -i C:\myScript.sql.
  3. Press ENTER.
Jul 27, 2021

What is difference between T-SQL and SQL? ›

SQL is a query language that serves the purpose of data manipulation. TSQL is a query language, but it is an extension of SQL that serves Microsoft SQL Server databases and software.

Is T-SQL difficult? ›

How Quickly Can You Learn SQL? Generally speaking, SQL is an easy language to learn. If you understand programming and already know some other languages, you can learn SQL in a few weeks. If you're a beginner, completely new to programming, it can take longer.

How do you query all tables in a database? ›

The easiest way to find all tables in SQL is to query the INFORMATION_SCHEMA views. You do this by specifying the information schema, then the “tables” view. Here's an example. SELECT table_name, table_schema, table_type FROM information_schema.

How do I create a SQL Server script without data? ›

Right-click on the database, then click on “Tasks” and choose the “Generate Scripts…” option.
...
Now, this is the part where you select the 3 script types:
  1. Schema only – Generate scripts without data i.e., schema only.
  2. Schema and data – Create scripts with data and schema.
  3. Data only – Create scripts with data only.
Nov 20, 2021

How do you create a script? ›

How to Generate Script in SQL Server 2014 With Data - YouTube

How do I find the indexes of a table in SQL Server? ›

Columns
  1. table_view - name of table or view index is defined for.
  2. object_type - type of object that index is defined for: Table. View.
  3. index_id - id of index (unique in table)
  4. type. Primary key. Unique. ...
  5. index_name - index name.
  6. columns - list of index columns separated with ","
  7. index_type - index type: Clustered index.
Jul 3, 2018

How do you create a query script in SQL Server? ›

Script a database by using the Generate Scripts option
  1. Connect to a server that's running SQL Server.
  2. Expand the Databases node.
  3. Right-click AdventureWorks2016 > Tasks > Generate Scripts:
  4. The Introduction page opens. ...
  5. Select Next to open the Set Scripting Options page. ...
  6. Select OK, and then select Next.
Jul 29, 2021

How many indexes can be created on a table in SQL Server? ›

Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX .

How will you check index fragmentation in SQL Server for all tables? ›

Basically all we need to do is the following: Open Sql Server Management Studio and connect to your desired server. Right click on the database in 'Object Explorer' for which you have to get all the index fragmentation. You can also write 'using <dbName>' in query editor before you run your query.

How do you check if indexes are being used in SQL? ›

Discoverying the use of the indexes

We need to discover which are the queries using these indexes. In order to do that, we can query the SQL Server Plan Cache, analysing the XML of each query plan and find where the indexes are being used. The DMV sys.

Which SQL command would be used to determine whether a query uses an index? ›

Write "explain " in front of your query. The result will tell you which indexes might be used.

What is SQL scripting language? ›

What is SQL Scripts? A SQL script is a set of SQL commands saved as a file in SQL Scripts. A SQL script can contain one or more SQL statements or PL/SQL blocks. You can use SQL Scripts to create, edit, view, run, and delete script files.

Why SQL scripts are used? ›

SQL Scripts can be used to edit, create, view, run, and delete script files. Remember the following when using SQL Scripts: SQL Scripts and SQL Commands have no interaction between each other. In a SQL script, SQL *Plus commands are ignored at run time.

What is the difference between query and script? ›

A query consists of a single command. A script is just a file with a bunch of queries.

When creating a database with SQL script what would you specify in the script? ›

A SQL script is a set of SQL commands saved as a file in SQL Scripts. A SQL script can contain one or more SQL statements or PL/SQL blocks. You can use SQL Scripts to create, edit, view, run, and delete database objects.

How do I run a SQL script in SQL Developer? ›

Executing a SQL Script from the SQL Scripts Page
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts. ...
  2. From the View list, select Details and click Go. ...
  3. Click the Run icon for the script you want to execute. ...
  4. The Run Script page appears. ...
  5. Click Run to submit the script for execution.

How do I run a SQL script in MySQL? ›

To run SQL script in MySQL, use the MySQL workbench. First, you need to open MySQL workbench. Now, File -> Open SQL Script to open the SQL script. Note − Press OK button twice to connect with MySQL.

How can I get all tables from SQL Server database? ›

Then issue one of the following SQL statement:
  1. Show all tables owned by the current user: SELECT table_name FROM user_tables;
  2. Show all tables in the current database: SELECT table_name FROM dba_tables;
  3. Show all tables that are accessible by the current user:

How do I reindex all tables in SQL Server? ›

SQL Server Management Studio
  1. In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  2. Expand the Tables folder.
  3. Expand the table on which you want to reorganize an index.
  4. Expand the Indexes folder.
  5. Right-click the index you want to reorganize and select Rebuild.

How do I get a list of table names in SQL Server? ›

In MySQL, there are two ways to find the names of all tables, either by using the "show" keyword or by query INFORMATION_SCHEMA. In the case of SQL Server or MSSQL, You can either use sys. tables or INFORMATION_SCHEMA to get all table names for a database.

Can you run a SQL script from command line? ›

Introduction. Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line. In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure.

Which utility can you use to execute Transact-SQL scripts from a command line? ›

The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. To use sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL.

How do I run a SQL script in PowerShell? ›

Script run from PowerShell ISE:
  1. $SQLServer = "TestServerOne"
  2. $db3 = "TestDB3"
  3. $selectdata = "SELECT Id, IdData FROM invokeTable"
  4. Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -InputFile "C:\Files\TSQL\run.sql"
  5. Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $selectdata.
Mar 18, 2019

What is the relationship between SQL and T-SQL? ›

MS SQL is simply a short version of the (complete) product name Microsoft SQL Server . (Similar to "MS Office", "MS Windows" or "MS Access"). T-SQL is the SQL dialect that the product Microsoft SQL Server is using - and is short for "Transact-SQL" (thanks Aaron for reminding me!)

What is T-SQL in stored procedure? ›

Stored procedures are a collection of Transact-SQL statements stored within the database. They are used to encapsulate oft-used queries, such as conditional statements, loops, and other powerful programming features. Stored procedures are similar to functions in high-level programming languages.

Why is it called Transact-SQL? ›

TSQL is called as transactional language which is used to define how the things should be done. SQL executes as a single statement at a time. TSQL can execute bunch of statements at a time using different programming features like looping and if else statements.

Is T-SQL worth learning? ›

If you're looking for your first job in data, it turns out knowing SQL is even more critical. For data analyst roles, SQL is again the most in-demand skill, listed in a whopping 61% of job posts. For data analyst roles on Indeed, SQL appears as follows: 1.7 times more than Python.

How long does it take to learn T-SQL? ›

Because SQL is a relatively simple language, learners can expect to become familiar with the basics within two to three weeks. That said, if you're planning on using SQL skills at work, you'll probably need a higher level of fluency. How quickly you achieve mastery will depend on your method of learning.

Should I learn SQL or T-SQL first? ›

Whether you're a database admin, a developer, or an engineer, T-SQL's complex syntax gives you the ability to do more. To enhance your SQL knowledge, you should immerse yourself in T-SQL first, not SQL.

How do you create a query script in SQL Server? ›

Script a database by using the Generate Scripts option
  1. Connect to a server that's running SQL Server.
  2. Expand the Databases node.
  3. Right-click AdventureWorks2016 > Tasks > Generate Scripts:
  4. The Introduction page opens. ...
  5. Select Next to open the Set Scripting Options page. ...
  6. Select OK, and then select Next.
Jul 29, 2021

How do I copy an index from one database to another? ›

All replies
  1. Open SSMS.
  2. Expand databases and select the database->tasks->generate scripts to launch the GSW.
  3. click next and set "Script Check Constraints" to true, "Script Indexes " to true, "ScriptDependencies" to true and set whatever is needed.
  4. Select the tables for which the script is needed.
  5. Click next ->next->finish.
Apr 5, 2009

How do I create a clustered index in SQL Server? ›

On the Table Designer menu, click Indexes/Keys. In the Indexes/Keys dialog box, click Add. Select the new index in the Selected Primary/Unique Key or Index text box. In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.

How do I backup an index in SQL Server? ›

Select * into - Does not copy indexes, constraints, etc. You should generate a script from SQL server management studio by right-clicking on the desired table -> Script table as -> create to -> New query windows. Change the table name as needed and run the script.

What is a SQL script example? ›

Intro to SQL Scripts - YouTube

How do I script a SQL database with all tables? ›

1 Answer
  1. Right click Database.
  2. Select All Tasks > Generate SQL Scripts.
  3. Click Show All.
  4. Check All Tables.
  5. Click the Formatting tab. Select the options you require.
  6. Click the Options tab. ...
  7. Then choose whether you want it all in one file or one file per object.
Feb 25, 2011

How do I create a SQL Server script without data? ›

Right-click on the database, then click on “Tasks” and choose the “Generate Scripts…” option.
...
Now, this is the part where you select the 3 script types:
  1. Schema only – Generate scripts without data i.e., schema only.
  2. Schema and data – Create scripts with data and schema.
  3. Data only – Create scripts with data only.
Nov 20, 2021

How do I copy a constraint from one table to another in SQL Server? ›

If you want two tables to be set up with the same constraints, you have to do it manually by running the create table/constraint statements. You can have sql server create the sql statements from the existing table though.

How do I transfer data from one database to another? ›

Migrate data from one database to another
  1. Create a source database Connection.
  2. Create a destination database Connection.
  3. Start creating a Flow by selecting Database to database in the Gallery.
  4. Add a new source to destination transformation.
  5. Define the transformation parameters when the source is a database.
Sep 21, 2021

How do I copy a table from one SQL Server database to another? ›

Launch SQL Server Management Studio. Select and right-click on the Source Database, go to Tasks > Export Data. Import/Export Wizard will be opened and click on Next to proceed. Enter the data source, server name and select the authentication method and the source database.

How many indexes can be created on a table in SQL? ›

Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX .

How many types of indexes can be created in MS SQL Server using T SQL? ›

There are two types of XML indexes, primary and secondary, which index all tags, values, paths and properties in the XML data in your column.

Does primary key automatically CREATE INDEX? ›

A primary index is automatically created for the primary key and ensures that the primary key is unique. You can use the primary index to retrieve and access objects from the database. The unique index is a column, or an ordered collection of columns, for which each value identifies a unique row.

What are the 3 types of backups? ›

The most common backup types are a full backup, incremental backup and differential backup. Other backup types include synthetic full backups and mirroring.

How do I use Ola Hallengren scripts? ›

How To: Use Ola Hallengren's SQL Server Maintenance Solution
  1. Execute MaintenanceSolution. sql. This script creates all the objects and jobs that you need.
  2. Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that these jobs are completing successfully. ...
  3. Schedule the jobs.
Mar 31, 2015

How do I automatically backup SQL Server database? ›

In Task Scheduler, right-click on Task Schedule Library and click on Create Basic task…. Enter the name for the new task (for example: SQLBackup) and click Next. Select Daily for the Task Trigger and click Next. Set the recurrence to one day and click Next.

Videos

1. SSMS Shortcuts - Indenting, commenting, scripting, and TSQL hacks
(Kendra Little)
2. Missing Index Hints in SQL Server Management Studio (by Amit Bansal)
(SQLMaestros)
3. introduction to sql server 2008 - create index using t-sql
(R.N.A. Creation)
4. SQL Server TSQL Generate Insert Script
(CKWTech LLC)
5. SQL Server tutorial 81: Rebuilding Indexes
(Johnny Deluca)
6. SQL Server Tutorial 13: Creating your first clustered index using SSMS
(Johnny Deluca)

Top Articles

You might also like

Latest Posts

Article information

Author: Kareem Mueller DO

Last Updated: 11/10/2022

Views: 5661

Rating: 4.6 / 5 (66 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Kareem Mueller DO

Birthday: 1997-01-04

Address: Apt. 156 12935 Runolfsdottir Mission, Greenfort, MN 74384-6749

Phone: +16704982844747

Job: Corporate Administration Planner

Hobby: Mountain biking, Jewelry making, Stone skipping, Lacemaking, Knife making, Scrapbooking, Letterboxing

Introduction: My name is Kareem Mueller DO, I am a vivacious, super, thoughtful, excited, handsome, beautiful, combative person who loves writing and wants to share my knowledge and understanding with you.