How to Get Table Script with Their all Indexes in SQL Server

This blog contains text about getting all index script of a specific sql table.

Every time when i create a copy table of any abc table (using select * into) it doesnt copy their indexes.

at that time i took single-single index which was very time taking. Now i got the solution .So I want to share this solution to you all.

at the time of script creation I have an option to choose script index true/false. which creates all clustered and non-clustered indexes of table.

Please go through with following screens to get table script with their indexes...

In above image sales_report table contains two non-clustered index IDX_GETSALES and IDX_GETDATE

Now I create a script of sales_report table which contains their all index script too.

Steps: Right click on you database - > Tasks - > Generate Scripts ->

Next - > Next ->

Set Script indexes =true

Check tables - > next

Check sales_report table - > next

Check script to new query window - > next - > finish.

It returns a query which contains table script and their indexes. Example script -

USE [super_new]
CREATE TABLE [dbo].[SALES_REPORT](
[DATE] [varchar](50) NULL,
[PARTICULARS] [varchar](max) NULL,
[GNAME] [varchar](200) NULL,
[BNAME] [varchar](200) NULL,
[PARTYNAME] [varchar](100) NULL,
[ADDRESS] [varchar](200) NULL,
[PINCODE] [varchar](10) NULL,
[PHONE] [varchar](20) NULL,
[MOBILE] [varchar](20) NULL,
[DOB] [varchar](50) NULL,
[EMAIL] [varchar](200) NULL,
[VCHTYPE] [varchar](200) NULL,
[VCHNO] [varchar](200) NULL,

) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IDX_GETDATE] ON [dbo].[SALES_REPORT]
(
[BNAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_GETSALES] ON [dbo].[SALES_REPORT]
(
[ADDRESS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

1 Comment

Join the conversation! Your thoughts help the community grow.

Roleigh Martin5y ago
Where is the create index ddl stored inside the database? Your solution requires manual clicking inside Sql Server Management Studio, I wish to be able to have a stored procedure do the same using system tables and dynamic sql, but can't figure out where the ddl for create index resides, I don't see a system information.schema view to use.