(一)组织示意图

Author：水如烟 

 

当前进度:

Public   Class  SqlDatabase
     Private  gLoginInformation  As   New  LzmTW.uSystem.uData.uSql.LoginInformation
     Private  gBuilder  As   New  Sql9Builder

     Sub   New ()
        gBuilder.ResetConnection(gLoginInformation.ConnectionStringBuilder.ConnectionString)
     End Sub

     Public   ReadOnly   Property  Builder()  As  Sql9Builder
         Get
             Return  gBuilder
         End   Get
     End Property
End Class

 

Public   Class  Form1
     Dim  gSql9Dbase  As   New  SqlDatabase
     Dim  DbBuilder  As  DbBuilder  =  gSql9Dbase.Builder

     Private   Sub  Button1_Click( ByVal  sender  As  System.Object,  ByVal  e  As  System.EventArgs)  Handles  Button1.Click
         Dim  ass  As  Reflection.Assembly  =  Reflection.Assembly.LoadFrom( " Sql2005BuilderDemo.dll " )
        DbBuilder.ReadInfoFrom( Of  Sql9CatalogInfo, Sql9TableInfo, Sql9FieldInfo)(ass)
        DbBuilder.CreateAll()
     End Sub

     Private   Sub  Button2_Click( ByVal  sender  As  System.Object,  ByVal  e  As  System.EventArgs)  Handles  Button2.Click
        DbBuilder.ReadInfoFrom( " Northwind " )
        DbBuilder.Catalog.Name  =   " Northwind1 "
        DbBuilder.CreateAll()
        Console.WriteLine(DbBuilder.GetAllScript)
     End Sub
End Class

NortrhWind1的输出脚本为(还不全面):

USE  master
GO
IF    EXISTS
(
     SELECT   *
     FROM  sys.databases
     WHERE  name  =  N ' Northwind1 '
)
DROP   DATABASE  Northwind1
GO
CREATE   DATABASE  Northwind1
GO

USE  Northwind1
GO
IF   NOT   EXISTS
(
     SELECT   *
     FROM  sys.schemas
     WHERE  name  =  N ' dbo '
)
EXEC  sys.sp_executesql N ' CREATE SCHEMA [dbo] AUTHORIZATION [dbo] '
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Categories] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Categories ]
GO
CREATE   TABLE   [ dbo ] . [ Categories ]
(
     [ CategoryID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL  ,
     [ CategoryName ]   [ nvarchar ]  ( 15 )  NOT   NULL  ,
     [ Description ]   [ ntext ]  ,
     [ Picture ]   [ image ]  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[CustomerCustomerDemo] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ CustomerCustomerDemo ]
GO
CREATE   TABLE   [ dbo ] . [ CustomerCustomerDemo ]
(
     [ CustomerID ]   [ nchar ]  ( 5 )  NOT   NULL  ,
     [ CustomerTypeID ]   [ nchar ]  ( 10 )  NOT   NULL  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[CustomerDemographics] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ CustomerDemographics ]
GO
CREATE   TABLE   [ dbo ] . [ CustomerDemographics ]
(
     [ CustomerTypeID ]   [ nchar ]  ( 10 )  NOT   NULL  ,
     [ CustomerDesc ]   [ ntext ]  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Customers] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Customers ]
GO
CREATE   TABLE   [ dbo ] . [ Customers ]
(
     [ CustomerID ]   [ nchar ]  ( 5 )  NOT   NULL  ,
     [ CompanyName ]   [ nvarchar ]  ( 40 )  NOT   NULL  ,
     [ ContactName ]   [ nvarchar ]  ( 30 ) ,
     [ ContactTitle ]   [ nvarchar ]  ( 30 ) ,
     [ Address ]   [ nvarchar ]  ( 60 ) ,
     [ City ]   [ nvarchar ]  ( 15 ) ,
     [ Region ]   [ nvarchar ]  ( 15 ) ,
     [ PostalCode ]   [ nvarchar ]  ( 10 ) ,
     [ Country ]   [ nvarchar ]  ( 15 ) ,
     [ Phone ]   [ nvarchar ]  ( 24 ) ,
     [ Fax ]   [ nvarchar ]  ( 24 ) 

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Employees] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Employees ]
GO
CREATE   TABLE   [ dbo ] . [ Employees ]
(
     [ EmployeeID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL  ,
     [ LastName ]   [ nvarchar ]  ( 20 )  NOT   NULL  ,
     [ FirstName ]   [ nvarchar ]  ( 10 )  NOT   NULL  ,
     [ Title ]   [ nvarchar ]  ( 30 ) ,
     [ TitleOfCourtesy ]   [ nvarchar ]  ( 25 ) ,
     [ BirthDate ]   [ datetime ]  ,
     [ HireDate ]   [ datetime ]  ,
     [ Address ]   [ nvarchar ]  ( 60 ) ,
     [ City ]   [ nvarchar ]  ( 15 ) ,
     [ Region ]   [ nvarchar ]  ( 15 ) ,
     [ PostalCode ]   [ nvarchar ]  ( 10 ) ,
     [ Country ]   [ nvarchar ]  ( 15 ) ,
     [ HomePhone ]   [ nvarchar ]  ( 24 ) ,
     [ Extension ]   [ nvarchar ]  ( 4 ) ,
     [ Photo ]   [ image ]  ,
     [ Notes ]   [ ntext ]  ,
     [ ReportsTo ]   [ int ]  ,
     [ PhotoPath ]   [ nvarchar ]  ( 255 ) 

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[EmployeeTerritories] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ EmployeeTerritories ]
GO
CREATE   TABLE   [ dbo ] . [ EmployeeTerritories ]
(
     [ EmployeeID ]   [ int ]   NOT   NULL  ,
     [ TerritoryID ]   [ nvarchar ]  ( 20 )  NOT   NULL  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Order Details] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Order Details ]
GO
CREATE   TABLE   [ dbo ] . [ Order Details ]
(
     [ OrderID ]   [ int ]   NOT   NULL  ,
     [ ProductID ]   [ int ]   NOT   NULL  ,
     [ UnitPrice ]   [ money ]   NOT   NULL  ,
     [ Quantity ]   [ smallint ]   NOT   NULL  ,
     [ Discount ]   [ real ]   NOT   NULL  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Orders] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Orders ]
GO
CREATE   TABLE   [ dbo ] . [ Orders ]
(
     [ OrderID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL  ,
     [ CustomerID ]   [ nchar ]  ( 5 ) ,
     [ EmployeeID ]   [ int ]  ,
     [ OrderDate ]   [ datetime ]  ,
     [ RequiredDate ]   [ datetime ]  ,
     [ ShippedDate ]   [ datetime ]  ,
     [ ShipVia ]   [ int ]  ,
     [ Freight ]   [ money ]  ,
     [ ShipName ]   [ nvarchar ]  ( 40 ) ,
     [ ShipAddress ]   [ nvarchar ]  ( 60 ) ,
     [ ShipCity ]   [ nvarchar ]  ( 15 ) ,
     [ ShipRegion ]   [ nvarchar ]  ( 15 ) ,
     [ ShipPostalCode ]   [ nvarchar ]  ( 10 ) ,
     [ ShipCountry ]   [ nvarchar ]  ( 15 ) 

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Products] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Products ]
GO
CREATE   TABLE   [ dbo ] . [ Products ]
(
     [ ProductID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL  ,
     [ ProductName ]   [ nvarchar ]  ( 40 )  NOT   NULL  ,
     [ SupplierID ]   [ int ]  ,
     [ CategoryID ]   [ int ]  ,
     [ QuantityPerUnit ]   [ nvarchar ]  ( 20 ) ,
     [ UnitPrice ]   [ money ]  ,
     [ UnitsInStock ]   [ smallint ]  ,
     [ UnitsOnOrder ]   [ smallint ]  ,
     [ ReorderLevel ]   [ smallint ]  ,
     [ Discontinued ]   [ bit ]   NOT   NULL  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Region] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Region ]
GO
CREATE   TABLE   [ dbo ] . [ Region ]
(
     [ RegionID ]   [ int ]   NOT   NULL  ,
     [ RegionDescription ]   [ nchar ]  ( 50 )  NOT   NULL  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Shippers] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Shippers ]
GO
CREATE   TABLE   [ dbo ] . [ Shippers ]
(
     [ ShipperID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL  ,
     [ CompanyName ]   [ nvarchar ]  ( 40 )  NOT   NULL  ,
     [ Phone ]   [ nvarchar ]  ( 24 ) 

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Suppliers] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Suppliers ]
GO
CREATE   TABLE   [ dbo ] . [ Suppliers ]
(
     [ SupplierID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL  ,
     [ CompanyName ]   [ nvarchar ]  ( 40 )  NOT   NULL  ,
     [ ContactName ]   [ nvarchar ]  ( 30 ) ,
     [ ContactTitle ]   [ nvarchar ]  ( 30 ) ,
     [ Address ]   [ nvarchar ]  ( 60 ) ,
     [ City ]   [ nvarchar ]  ( 15 ) ,
     [ Region ]   [ nvarchar ]  ( 15 ) ,
     [ PostalCode ]   [ nvarchar ]  ( 10 ) ,
     [ Country ]   [ nvarchar ]  ( 15 ) ,
     [ Phone ]   [ nvarchar ]  ( 24 ) ,
     [ Fax ]   [ nvarchar ]  ( 24 ) ,
     [ HomePage ]   [ ntext ]  

)
GO

IF    EXISTS
(
     SELECT   *
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID (N ' [dbo].[Territories] ' )
     AND  type  in  (N ' U ' )
)
DROP   TABLE   [ dbo ] . [ Territories ]
GO
CREATE   TABLE   [ dbo ] . [ Territories ]
(
     [ TerritoryID ]   [ nvarchar ]  ( 20 )  NOT   NULL  ,
     [ TerritoryDescription ]   [ nchar ]  ( 50 )  NOT   NULL  ,
     [ RegionID ]   [ int ]   NOT   NULL  

)
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Categories] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Categories] ADD CONSTRAINT PK_dbo_Categories PRIMARY KEY (CategoryID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[CustomerCustomerDemo] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[CustomerCustomerDemo] ADD CONSTRAINT PK_dbo_CustomerCustomerDemo PRIMARY KEY (CustomerID,CustomerTypeID) '
EXEC  sys.sp_executesql  @Sql

GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[CustomerDemographics] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[CustomerDemographics] ADD CONSTRAINT PK_dbo_CustomerDemographics PRIMARY KEY (CustomerTypeID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Customers] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_dbo_Customers PRIMARY KEY (CustomerID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Employees] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Employees] ADD CONSTRAINT PK_dbo_Employees PRIMARY KEY (EmployeeID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[EmployeeTerritories] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[EmployeeTerritories] ADD CONSTRAINT PK_dbo_EmployeeTerritories PRIMARY KEY (EmployeeID,TerritoryID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Order Details] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Order Details] ADD CONSTRAINT PK_dbo_Order_Details PRIMARY KEY (OrderID,ProductID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Orders] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Orders] ADD CONSTRAINT PK_dbo_Orders PRIMARY KEY (OrderID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Products] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Products] ADD CONSTRAINT PK_dbo_Products PRIMARY KEY (ProductID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Region] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Region] ADD CONSTRAINT PK_dbo_Region PRIMARY KEY (RegionID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Shippers] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Shippers] ADD CONSTRAINT PK_dbo_Shippers PRIMARY KEY (ShipperID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Suppliers] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Suppliers] ADD CONSTRAINT PK_dbo_Suppliers PRIMARY KEY (SupplierID) '
EXEC  sys.sp_executesql  @Sql
GO

DECLARE   @Table   nvarchar ( 50 )
DECLARE   @object_id   int
DECLARE   @Sql   nvarchar ( MAX )

SET   @table   =  N ' [dbo].[Territories] '

SELECT    @object_id   =   object_id
FROM  sys.objects
WHERE  parent_object_id  =  
(
     SELECT   object_id
     FROM  sys.objects
     WHERE   object_id   =   OBJECT_ID ( @table )
     AND  type  in  (N ' U ' )
    )
AND  type  in  (N ' PK ' )

IF   @object_id   >   0
BEGIN
     SET   @Sql   =  N ' ALTER TABLE  '   +   @table   +  N '  DROP CONSTRAINT  '   +   OBJECT_name ( @object_id )
     EXEC  sys.sp_executesql  @Sql
END

SET   @Sql   =  N ' ALTER TABLE [dbo].[Territories] ADD CONSTRAINT PK_dbo_Territories PRIMARY KEY (TerritoryID) '
EXEC  sys.sp_executesql  @Sql
GO