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
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/
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.
how to extract the schema alone of a table
Posted - 10/04/2012 : 03:28:24
