August 8, 2005 at 12:13 am
Hi friends,
Is it possible to get the CREATE TABLE script (with constraints) and also not with EM.
August 8, 2005 at 2:57 am
Yes, use Query Analyser. Open the Object Browser and browse to the table you wish to script. Right click it and select 'Script object to new window as / Create' and that should include the constraints.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 8, 2005 at 3:09 am
Thnx..
But not with Query Analyser also.
Is there any SYSTEM VIEW which provides the script???
or
Can i get the columns details with PRIMARY key, it's FOREIGN key, IDENTITY columns.
August 8, 2005 at 3:15 am
Take a look at the INFORMATION_SCHEMA views in BOL - you'll get the constraints from there. But the actual CREATE script - not available from a system view as far as I know.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 8, 2005 at 9:55 pm
If you're trying to achieve this programmatically you will either have to plough through the system tables or INFORMATION_SCHEMA views, or use the Script method of a SQL-DMO Table object.
You can get the details from SQL Server Books Online or I can send you some sample code.
Regards
August 8, 2005 at 10:52 pm
I have checked INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. I m able to get the primary key column. But how to determine the Foreign Key column i.e., to which table it belongs!!
It will nice if u send me some sample code!
thnx.
August 9, 2005 at 6:38 am
please stop thi stupid discussions
August 9, 2005 at 7:57 am
You can figure out how to get this information from the system tables, but since this is the Programming forum I think what you're looking for is SQL-DMO like Andrew suggested. It can be used create scripts like Enterprise Manager, with even more options for what is included in the script. (All the scripting functionality of Enterprise Manager is based on SQL-DMO.)
This example is one way to use the Script method for tables. The DRI_All option tells it to include foreign keys, defaults, unique keys, etc For more info refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_5e2a.asp .
Dim oServer As SQLDMO.SQLServer2
Dim oTable As SQLDMO.Table2
Dim sCreateTable As String
Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "<server>"
Set oTable = oServer("<database>").Tables("<table>")
sCreateTable = oTable.Script(SQLDMOScript_DRI_All + SQLDMOScript_Indexes _
+ SQLDMOScript_OwnerQualify + SQLDMOScript_Triggers, , , _
SQLDMOScript2_ExtendedProperty + SQLDMOScript2_FullTextIndex _
+ SQLDMOScript2_NoCollation + SQLDMOScript2_NoFG)
August 9, 2005 at 10:24 pm
Hi Vinod!!!
I hope u r feeling well!!!
May i know why this discussion is "stupid" for u!!!
Tell me if u know the answer for the question!!!
thnx!!!
August 11, 2005 at 4:23 pm
sysforeignkeys SQL system table has the info. But it only the id's of the objects. You will have to build the statement yourself.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply