how to extract the schema alone of a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to extract the schema alone of a table

Author

Topic

arthiasha
Starting Member

40 Posts

Posted - 2012-10-04 : 03:28:24

i have a collection of creation of tables script where i have to extract the structure of one particular table alone.
Which command should be used to extract the structure of a table?
When i run the command with the table name it should automatically call the create function and create the structure of the table.
i tried with this
select * into newtablename from oldtablename;
but this is just the copy of another table.

I have saved the script for table creation in my local system.
So which command should i use to call the create function so that it automatically creates the table structure in my db

USE [mytable]
GO

/****** Object: Table [dbo].[module] Script Date: 10/04/2012 12:52:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[module](
[module_id] [varchar](15) NOT NULL,
[module_name] [varchar](30) NOT NULL,
[last_update_id] [nvarchar](12) NOT NULL,
[last_update_timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_module_1] PRIMARY KEY CLUSTERED
(
[module_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

this create function should be called automatically

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 06:58:26

You can add a TOP 0 clause to the SELECT INTO that you tried.

select TOP 0 * into newtablename from oldtablename;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 10:52:09

please keep in mind that SELECT INTO...just created table structure alone so if there are any indexes,constraints etc you want to copy to new table you need to script out them from main table and do it manually.

or another option would be to use generate scripts wizard to include all the details and then apply them to create new copy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arthiasha
Starting Member

40 Posts

Posted - 2012-10-05 : 05:12:11

Thanks a lot,
I tried and it worked...

Is there any system procedure command to call the create function?
It has to be done dynamically

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-05 : 06:49:50

The commands that would create a table are either the "SELECT ... INTO" or the "CREATE TABLE ..." commands.

Can you explain more on what you meant by "table has to be created dynamically"? In most cases, tables are relatively static entities - i.e., you don't/shouldn't have to create them every day. It is the data that can and would change frequently. In your situation if that is not the case, there may be room for design improvements.

If you can describe the business problem you are trying to solve along with some sample data, many experts on this forum would be able to offer you their thoughts and advice.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-05 : 10:16:32

Try this http://beyondrelational.com/modules/2/blogs/70/posts/10793/generate-sql-script.aspx and you can customise it to get index information too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources