identity and computed columns definition

Hello
I am looking hints where database store information about:
- identity definition ( start value, step)
- definiton of computed column
Sometimes I have to copy a few tables structure with some modifications
but simple
select * into dest from src where 1=2
does not copy computed columns, default values etc..
Can anybody help?
YaroYaro
Yes , this approach has some limitations. Try DTS package to move the
objects
"Yaro" <yarok_delthisdes_@.op.pl> wrote in message
news:e08h52$i11$1@.83.238.170.160...
> Hello
> I am looking hints where database store information about:
> - identity definition ( start value, step)
> - definiton of computed column
> Sometimes I have to copy a few tables structure with some modifications
> but simple
> select * into dest from src where 1=2
> does not copy computed columns, default values etc..
> Can anybody help?
> Yaro
>|||Unfortunatelly, I can't use DTS :( (business restrictions, etc)
I have to do my job using Transact-SQL and I am rather looking for info
about
"magic" procedure or system view.
Y.
> Yes , this approach has some limitations. Try DTS package to move the
> objects
>> Hello
>> I am looking hints where database store information about:
>> - identity definition ( start value, step)
>> - definiton of computed column
>> Sometimes I have to copy a few tables structure with some modifications
>> but simple
>> select * into dest from src where 1=2
>> does not copy computed columns, default values etc..
>> Can anybody help?
>> Yaro|||Yaro
Well , I'd use a SQLDMO objects library to generate script of the objects .
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
'==========================================================================' Parameters
' ----
' strLogin: The Login Name of the account you use to connect to the server
' strPwd: The Password for the account you use to connect to the server
' strDataBase: The name of the database you want to create script for
' StrFilePath: The path and filename to store the SQL file
'==========================================================================
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script User Defined Data Types
For Each genObj In db.UserDefinedDatatypes
genObj.Script intOptions, StrFilePath
Next
' Script Tables and Triggers, ignoring system
' tables and system generated triggers
For Each genObj In db.Tables
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
For Each objTrigger In genObj.Triggers
If objTrigger.SystemObject = False Then
objTrigger.Script intOptions, StrFilePath
End If
Next
End If
Next
' Script Rules
For Each genObj In db.Rules
genObj.Script intOptions, StrFilePath
Next
' Script Defaults
For Each genObj In db.Defaults
genObj.Script intOptions, StrFilePath
Next
' Script Sprocs, ignoring system sprocs
For Each genObj In db.StoredProcedures
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
Save the module as MyModule.
To call the procedure, open the Immediate window, type the following line,
and then press ENTER:Call
ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Yaro" <yarok_delthisdes_@.op.pl> wrote in message
news:e08mk5$8g7$1@.83.238.170.160...
> Unfortunatelly, I can't use DTS :( (business restrictions, etc)
> I have to do my job using Transact-SQL and I am rather looking for info
> about
> "magic" procedure or system view.
> Y.
>> Yes , this approach has some limitations. Try DTS package to move the
>> objects
>
>> Hello
>> I am looking hints where database store information about:
>> - identity definition ( start value, step)
>> - definiton of computed column
>> Sometimes I have to copy a few tables structure with some modifications
>> but simple
>> select * into dest from src where 1=2
>> does not copy computed columns, default values etc..
>> Can anybody help?
>> Yaro
>
>