#眉標=SQL Server 2008、SMO、DMO
#副標=實用SQL Server 2008（2）
#大標=程式化產生資料庫內物件的T-SQL Script
#作者=文／圖		胡百敬

===<反灰>=============
http://cid-bf14192bd27975cb.skydrive.live.com/self.aspx/Source/SQL%202008/GenerateSQLScriptWeb.zip
==<end>==============


===<反灰>=============
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-tw&FamilyID=c6c3e9ef-ba29-4a43-8d69-a2bed18fe73c
==<end>==============


===<反灰>=============
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
==<end>==============


==<box>===========
程式1
'---連接到本機預設的執行個體
Dim sc As New Microsoft.SqlServer.Management.Common.ServerConnection(".")
Dim svr As New Server(sc)

'---取得 Northwind 資料庫
Dim db As Database = svr.Databases("Northwind")

'---設定產生 Script 的相關屬性
Dim so As New ScriptingOptions()

Const strPath As String = "C:\temp\"
Dim strFile As String = ""
For Each sp As StoredProcedure In db.StoredProcedures
    '---系統物件不要產生，加密過的物件無法產生
    If (Not sp.IsEncrypted And Not sp.IsSystemObject) Then
        strFile = strPath + sp.Schema & "." & sp.Name & ".sql"
        '產生的 Script 語法可能有中文字，所以寫出要用 Unicode，否則 SSMS 讀取會有問題
        Dim sw As New StreamWriter(strFile, False, System.Text.Encoding.Unicode)

        '先產生 Drop 語法
        '存在，才刪除，或不存在，才建立
        so.IncludeIfNotExists = True
        '產生刪除物件的語法
        so.ScriptDrops = True
        '每種物件都有 Script 方法，但是透過 StringCollection 物件傳回 Script
        Dim strCol As System.Collections.Specialized.StringCollection
        strCol = sp.Script(so)
        For Each strScript As String In strCol
            sw.WriteLine(strScript)
        Next

        sw.WriteLine("GO")

        '再產生 Create 語法
        so.IncludeIfNotExists = False
        so.ScriptDrops = False
        strCol = sp.Script(so)
        For Each strScript As String In strCol
            sw.WriteLine(strScript)
        Next

        sw.Close()
    End If
Next
==<end>==============



==<box>===========
程式2
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustOrderHist]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CustOrderHist]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
==<end>==============



===<反灰>=============
exec sp_helptext 'HumanResources.Employee','OrganizationLevel'
==<end>==============



===<反灰>=============
exec sp_helptext 'HumanResources.vEmployee'
==<end>==============



==<box>===========
程式3
CREATE PROC sp_ScriptObject @ObjectName nvarchar(200)
AS
DECLARE @t TABLE([text] nvarchar(max))
INSERT @t EXEC('exec sp_helptext ''' + @ObjectName + '''')
DECLARE @sql nvarchar(max)
SET @sql=''
SELECT @sql=@sql + [TEXT] FROM @t
SELECT @sql
==<end>==============



==<box>===========
程式4
exec sp_executesql N'SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'Ten Most Expensive Products',@_msparam_4=N'dbo'
==<end>==============



===<反灰>=============
http://techrepublic.com.com/http://techrepublic.com.com/5208-13400-0.html?forumID=102&threadID=271777&messageID=2609290
==<end>==============



==<box>===========
程式5

'透過 Scripter 產生 Script
Dim scp As New Scripter(svr)
'設定要一併產生參照的資料表之 T-SQL
scp.Options.WithDependencies = False
'同時要建立索引
scp.Options.Indexes = True
'建立主鍵定義
scp.Options.DriPrimaryKey = True
'Scripter 的 Script 函數需要 SqlSmoObject 物件陣列當參數
Dim smoObjs(0) As SqlSmoObject

For Each tb As Table In db.Tables
    If Not tb.IsSystemObject Then
        smoObjs(0) = tb
        strFile = strPath + tb.Schema & "." & tb.Name & ".sql"
        Dim sw As New StreamWriter(strFile, False, System.Text.Encoding.Unicode)
        For Each strScript As String In scp.Script(smoObjs)
            sw.WriteLine(strScript)
            sw.WriteLine("GO" & vbCrLf)
        Next
        sw.Close()
    End If
Next
==<end>==============




==<box>===========
程式6

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customers](
	[CustomerID] [nchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[CompanyName] [nvarchar](40) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[ContactName] [nvarchar](30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[ContactTitle] [nvarchar](30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[Address] [nvarchar](60) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[City] [nvarchar](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[Region] [nvarchar](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[PostalCode] [nvarchar](10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[Country] [nvarchar](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[Phone] [nvarchar](24) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[Fax] [nvarchar](24) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[CustomerID] 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

CREATE NONCLUSTERED INDEX [City] ON [dbo].[Customers] 
(
	[City] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
……
===<反灰>=============




==<box>===========
程式7
--要比較的物件(Comp 開頭)來自Linked Server
--Linked Server 是開發環境，所以是superset(採Full Join)
use master
go

--因為是組字串產生要執行的 T-SQL 語法，所以會有 SQL Injection 的危險
--由於程式是給開發和管理人員使用，屬於內部的應用程式，維護方便性優於安全
--也就未對此進一步防範
CREATE PROC sp_GetObjectList 
@Type varchar(5)='p',@Schema sysname='%',@ObjectName nvarchar(100)='%',
@LinkServerName nvarchar(100)='[SQL2K2\I2]',@DB sysname='Northwind'
AS
	CREATE TABLE #Objs(CompSchemaName sysname,CompName sysname,CompCreate_Date DateTime,
	CompModify_Date DateTime,CompChecksum INT)

	declare @sql nvarchar(4000)
	SET @sql='select s.Name SchemaName,o.Name,Create_Date,Modify_Date,CHECKSUM(definition) 
	from @LinkServerName.@dbName.sys.objects o 
	join @LinkServerName.@dbName.sys.schemas s on o.Schema_id=s.Schema_id
	left join @LinkServerName.@dbName.sys.sql_modules AS sm ON sm.object_id = o.object_id
	WHERE TYPE=''@Type'' AND s.schema_id<16384 AND o.is_ms_shipped=0'
	SET @sql=REPLACE(@sql,'@LinkServerName',@LinkServerName)
	SET @sql=REPLACE(@sql,'@dbName',@db)
	SET @sql=REPLACE(@sql,'@Type',@Type)

	--print @sql
	INSERT #Objs EXEC(@sql)
	--計算程式碼的Checksum 以比對兩者間是否相同
	select 
	CASE WHEN CHECKSUM(Definition)=CompCheckSum THEN NULL ELSE N'不同' END [Checksum],
	SchemaName,Name,
	'ShowScript.aspx?Dest=Org&Name=' + SchemaName + '.' + Name AS URL,
	Create_Date,Modify_Date, 
	CompSchemaName,CompName,
	'ShowScript.aspx?Dest=Comp&Name=' + CompSchemaName + '.' + CompName AS CompURL,
	CompCreate_Date,CompModify_Date
	from 
	(
	SELECT CompSchemaName,CompName,CompCreate_Date,CompModify_Date,CompChecksum
	FROM #Objs) c
	FULL JOIN 
	(
	SELECT Schema_Name(Schema_ID) SchemaName,Name,Create_Date,Modify_Date,Definition 
	FROM sys.objects o
	LEFT JOIN sys.sql_modules AS sm ON sm.object_id = o.object_id
	WHERE TYPE=@Type AND SCHEMA_NAME(schema_id) LIKE @Schema AND name LIKE @ObjectName  AND o.is_ms_shipped=0
	) s
	ON s.SchemaName=c.CompSchemaName AND s.Name=c.CompName
	WHERE (CompSchemaName LIKE @Schema AND CompName LIKE @ObjectName) OR (CompName IS NULL)
==<end>==============





==<box>===========
程式8
CREATE PROC sp_GetSchemaList
AS
--不要系統已經存在的schema
select name from sys.schemas where schema_id<16384  
==<end>==============



===<反灰>=============
exec sp_ms_marksystemobject sp_GetObjectList
exec sp_ms_marksystemobject sp_GetSchemaList 
exec sp_ms_marksystemobject sp_ScriptObject
==<end>==============



==<box>===========
程式9
<appSettings>
    <!--依據開發環境建立物件的 T-SQL Script 檔案存放目錄-->
	<add key="Folder" value="Scripts"/>
</appSettings>
<connectionStrings>
   <!--需要建立 sp_GetObjectList、sp_ScriptObject 的測試環境 -->
   <add name="ObjSource" connectionString="Data Source=.;Initial Catalog=Northwind;User Id=sa;Password=P@ssw0rd" providerName="System.Data.SqlClient"/>

   <!--需要建立 sp_GetSchemaList、sp_ScriptObject 的開發環境 -->
   <add name="ObjCompare" connectionString="Data Source=.\I2;Initial Catalog=Northwind;User Id=sa;Password=P@ssw0rd" providerName="System.Data.SqlClient"/>
</connectionStrings>
==<end>==============



==<box>===========
程式10
private int GenerateScript(bool bolDropCreate)
{
    StringBuilder sb = new StringBuilder();
    int j = 0;
    for (int i=0; i < GridView1.Rows.Count; i++)
    {
        //若不是從 SqlDataSource 繫結，則採用以下的語法
        //if (Request.Form[GridView1.Rows[i].FindControl("CheckBox1").UniqueID]!=null) 
                
        if(GridView1.Rows[i].FindControl("CheckBox1") !=null)
        {
            //有勾選要建立該物件的 Script，且該物件不是空的
            if (((CheckBox)GridView1.Rows[i].FindControl("CheckBox1")).Checked && 
                GridView1.Rows[i].Cells[2].Text !="&nbsp;")
            {
                sb.Append(GridView1.Rows[i].Cells[2].Text + "." +
                    ((HyperLink)GridView1.Rows[i].FindControl("HyperLink2")).Text + ",");
                j++;
            }
        }
    }
    if (sb.Length == 0)
    {
        return 0;
    }
    //去掉結尾的逗號
    sb.Remove(sb.Length-1,1);
            
    DatabaseScripter ds = new DatabaseScripter();
    ds.ScriptAsCreate = bolDropCreate;

    switch (DropDownList1.SelectedValue)
    {
        case "p":
            ds.SprocsFilter = sb.ToString().Split(',');
            break;
        case "fn":
            ds.UdfsFilter = sb.ToString().Split(',');
            break;
        case "v":
            ds.ViewsFilter=sb.ToString().Split(',');
            break; 
    }
    //呼叫包裝 SMO 產生物件 Script 的類別
    //傳入據以建立物件的開發環境 SQL Server 之連接字串
    //以及存放 T-SQL Script 檔的目錄    ds.GenerateScript(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ObjCompare"].ConnectionString,        Server.MapPath( System.Web.Configuration.WebConfigurationManager.AppSettings["Folder"]),false);
    return j;
}
==<end>==============