SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to extract the schema alone of a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arthiasha
Starting Member

India
40 Posts

Posted - 10/04/2012 :  03:28:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/04/2012 :  06:58:26  Show Profile  Reply with Quote
You can add a TOP 0 clause to the SELECT INTO that you tried.
select TOP 0 * into newtablename from oldtablename;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/04/2012 :  10:52:09  Show Profile  Reply with Quote
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/

Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/05/2012 :  05:12:11  Show Profile  Reply with Quote
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

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/05/2012 :  06:49:50  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 10/05/2012 :  10:16:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000