I want to get the "CREATE" script for all tables.
Is there any script, like sp_helptext?
How can I get the script called when "Script Table as" -> "Create table" is selected?
-
You are interested in schema only. Not data. Right ?TarasB– TarasB2010-12-18 19:26:47 +00:00Commented Dec 18, 2010 at 19:26
-
Yes,firstly i want to get the schema. Getting data is another step.maycil– maycil2010-12-18 21:33:35 +00:00Commented Dec 18, 2010 at 21:33
Add a comment
|
4 Answers
You can right click on the database and go to Tasks -> Generate Scripts.
This will walk you through scripting out all of the scripts you want.
2 Comments
maycil
you are right. Generate Scripts is doing well done this job. But because of some authentication problem, i cannot use this solution.
Philip Kelley
Is the authentication problem one where you cannot access the table definitions? If so, then no tool will allow you to script the tables. Step one would seem to be to get the necessary access rights.
You may use Generate script option in SQL Server.
Tasks->Generate Scripts
There you can take script as your needs.
answered Dec 20, 2010 at 4:53
Nithesh Narayanan
11.8k35 gold badges102 silver badges140 bronze badges
Comments
You could use what Stefan and gbn have suggested. Additionally if u want scripts for the exact replica for ur DB then look at Microsoft SQL Server Database Publishing Wizard. It comes built into VS 2008 +
1 Comment
maycil
is there a new version? it isnot support sql 2008
Already somebody give the right answer to this question
The best method is Rightclick the database then Tasks->Generate Scripts
But you are point out because of some reasons you cannot use the method.
if you are looking for a method using query then you can take the below method it will return the schema of your table
Go
Create Function ShowMyTableData
(
@vsTableName varchar(50)
)
Returns
VarChar(Max)
With ENCRYPTION
Begin
Declare @ScriptCommand varchar(Max)
Select @ScriptCommand =
' Create Table [' + SO.name + '] (' + o.list + ')'
+
(
Case
When TC.Constraint_Name IS NULL
Then ''
Else 'ALTER TABLE ' + SO.Name + ' ADD CONSTRAINT ' +
TC.Constraint_Name + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')'
End
)
From sysobjects As SO
Cross Apply
(
Select
' [' + column_name + '] ' +
data_type +
(
Case data_type
When 'sql_variant'
Then ''
When 'text'
Then ''
When 'decimal'
Then '(' + Cast( numeric_precision_radix As varchar ) + ', ' + Cast( numeric_scale As varchar ) + ') '
Else Coalesce( '(' +
Case
When character_maximum_length = -1
Then 'MAX'
Else Cast( character_maximum_length As VarChar )
End + ')' , ''
)
End
)
+ ' ' +
(
Case
When Exists (
Select id
From syscolumns
Where
( object_name(id) = SO.name )
And
( name = column_name )
And
( columnproperty(id,name,'IsIdentity') = 1 )
)
Then 'IDENTITY(' +
Cast( ident_seed(SO.name) As varchar ) + ',' +
Cast( ident_incr(SO.name) As varchar ) + ')'
Else ''
End
) + ' ' +
(
Case
When IS_NULLABLE = 'No'
Then 'NOT '
Else ''
End
) + 'NULL ' +
(
Case
When information_schema.columns.COLUMN_DEFAULT IS NOT NULL
Then 'DEFAULT ' + information_schema.columns.COLUMN_DEFAULT
ELse ''
End
) + ', '
From information_schema.columns
Where
( table_name = SO.name )
Order by ordinal_position
FOR XML PATH('')) o (list)
Inner Join information_schema.table_constraints As TC On (
( TC.Table_name = SO.Name )
AND
( TC.Constraint_Type = 'PRIMARY KEY' )
And
( TC.TABLE_NAME = @vsTableName )
)
Cross Apply
(
Select '[' + Column_Name + '], '
From information_schema.key_column_usage As kcu
Where
( kcu.Constraint_Name = TC.Constraint_Name )
Order By ORDINAL_POSITION
FOR XML PATH('')
) As j (list)
Where
( xtype = 'U' )
AND
( Name NOT IN ('dtproperties') )
Return @ScriptCommand
End
you can call your function like
Select [dbo].ShowMyTableData ('tablename')
Extension :
if you need data too then you can use the following query
select 'insert into tablename values('+yourcolumnanme+','+columnanme2.....+')' from tablename
