Post
Ask Question

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...

1.jpg

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.

2.jpg

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

3.jpg
 

Next  - > Next ->

4.jpg

Set Script indexes =true

5.jpg

Check tables - > next

6.jpg

Check sales_report table - > next

7.jpg

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