Hello, I am using SQL 2005, I right-clicked a database's tables and saved its table scripts as a text file or sent it to a new Query window.

Question

Hello, I am using SQL 2005, I right-clicked a database's tables and saved its table scripts as a text file or sent it to a new Query window.

onlo

SSCertifiable

Points: 5080
More actions
April 14, 2010 at 11:16 pm

#386709

Hello, I am using SQL 2005, I right-clicked a database's tables and saved its table scripts as a text file or sent it to a new Query window.
However, why the output didn't have its non-clustered indexes or unique constraints ?
Consequently, I couldn't create some foregin keys.
Please tell me how can I script a database's tables including their non-clustered indexes and/or unique constraints.
I don't know SQL 2008 also has this problem.
If no, please ignore my post.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

I'm using SQL 2005 Express, and scripting the dbo.backupFile table on msdb, scripts out everything, columns, keys, indexes etc.

USE [msdb]

GO

/****** Object: Table [dbo].[backupfile] Script Date: 04/15/2010 07:26:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[backupfile](

[backup_set_id] [int] NOT NULL,

[first_family_number] [tinyint] NULL,

[first_media_number] [smallint] NULL,

[filegroup_name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[page_size] [int] NULL,

[file_number] [numeric](10, 0) NOT NULL,

[backed_up_page_count] [numeric](10, 0) NULL,

[file_type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[source_file_block_size] [numeric](10, 0) NULL,

[file_size] [numeric](20, 0) NULL,

[logical_name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[physical_drive] [nvarchar](260) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[physical_name] [nvarchar](260) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[state] [tinyint] NULL,

[state_desc] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[create_lsn] [numeric](25, 0) NULL,

[drop_lsn] [numeric](25, 0) NULL,

[file_guid] [uniqueidentifier] NULL,

[read_only_lsn] [numeric](25, 0) NULL,

[read_write_lsn] [numeric](25, 0) NULL,

[differential_base_lsn] [numeric](25, 0) NULL,

[differential_base_guid] [uniqueidentifier] NULL,

[backup_size] [numeric](20, 0) NULL,

[filegroup_guid] [uniqueidentifier] NULL,

[is_readonly] [bit] NULL,

[is_present] [bit] NULL,

PRIMARY KEY CLUSTERED

(

[backup_set_id] ASC,

[file_number] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[backupfile] WITH CHECK ADD FOREIGN KEY([backup_set_id])

REFERENCES [dbo].[backupset] ([backup_set_id])

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

onlo SSCertifiable Points: 5080 More actions · Answer 2

But your script still didn't include non-clustered indexes.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

Yes, because there aren't any.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

onlo SSCertifiable Points: 5080 More actions · Answer 4

But can you tell me how to use SQL Management Studio to script a database's tables INCLUDING its non-clustered indexes and unique index constraints, it is vital to a database, without them, its foreign key generation might be failed.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 5

script out the keys, NC Indexes, etc and add them to the bottom of your script (create table script)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

onlo SSCertifiable Points: 5080 More actions · Answer 6

onlo

SSCertifiable

Points: 5080

April 15, 2010 at 12:51 am

#1151908

Thanks.

I fount out the answer for my question.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 7

and what was it?

it's always good practice to explain the solution, whenever we can't assist you, that way, we learn as well.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

onlo SSCertifiable Points: 5080 More actions · Answer 8

I used Object Explorer Detail windows, it doesn't give me option to include or exclude the indexes.

But if I right-clicked a database and choose 'Tasks', 'Generate Scripts ...', I will have the rights to include or not include indexes.