一个 .NET Windows 应用程序，用于生成 SQL Server 脚本
2.00/5 (4投票s)
生成 SQL Server 存储过程和表定义脚本
引言
如果你需要为大量的存储过程生成创建脚本，你会怎么做？ 使用 Sp_Helptext SQL Server 内置存储过程。 但这会消耗时间。本文介绍如何生成 SQL Server 存储过程和创建表定义脚本。 我制作了一个 Windows 应用程序，只需单击一个按钮即可生成 SQL Server 存储过程和创建表创建脚本。
使用代码
在 Visual Studio 2008 中打开一个新的 Windows 窗体应用程序项目。 将其命名为 GenerateScripts
在 Windows 窗体上拖动一个名为 DBObjects 的 GroupBox。 在 Group box 内部拖动两个单选按钮，分别命名为“存储过程”和“表”。
还拖动 2 个标签、3 个文本框和 3 个按钮。
将标签命名为“连接字符串”和“路径”。 将按钮命名为“生成脚本”、“清除”和“关闭”。
单击“生成脚本”按钮时，编写以下代码
private void btnGenerateScript_Click(object sender, EventArgs e)
{
if (txtScriptsName.Text.Trim() == "" || txtConnString.Text.Trim() == "") ) //Validating Script and Connection String Textbox
{
MessageBox.Show("Enter Script Name");
}
else
{
string ScriptName = txtScriptsName.Text.Trim();
char[] a = { '\n' };
string[] Finalstr = ScriptName.Split(a); //Splitting by new line character
if (rdbStoredProc.Checked) //Code For Stored Proc Script Generation
{
string Extn = "";
foreach (string s in Finalstr)
{
string ScriptString = s.TrimEnd('\r');
Extn = Path.GetExtension(ScriptString);
if (Extn.ToUpper() == ".SQL") //Checking For Valid Extension
{
GenerateStoredProc(ScriptString.Replace(".sql","")); //Calling Generate Procedure Method
}
else
{
MessageBox.Show("Invalid File Extension. Enter Script Name With .sql Extension")
}
}
}
else // This part will be called when user has checked Table radiobutton
{
foreach(string s in Finalstr)
{
GenerateTableScripts(s);
}
}
}
MessageBox.Show(@"Scripts Generated Successfully");
}
//Following Method produces SQL Server Stored Procedure defination scripts.
private void GenerateStoredProc(string ProcName)
{
if (System.IO.Directory.Exists(txtPath.Text.Trim()) == false) //Checking For the Path entered by user exists or not
{
System.IO.Directory.CreateDirectory(txtPath.Text.Trim()); //If Path does not exist then create it
}
SqlConnection con = new SqlConnection(txtConnString.Text.Trim());
SqlCommand cmd = new SqlCommand("SP_HELPTEXT " + ProcName, con);
cmd.CommandType = CommandType.Text;
cmd.Connection.Open();
SqlDataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
string path = txtPath.Text.Trim() + ProcName + ".sql";
FileStream fs = new FileStream(txtPath.Text.Trim() + ProcName + ".sql", FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter sw = new StreamWriter(fs, Encoding.Default);
while (dr.Read())
{
sw.WriteLine(dr[0].ToString());
}
sw.Close();
fs.Close();
}
以下函数方法生成创建表脚本以及该表上的所有索引脚本。
private void GenerateTableScripts(string TableName)
{
if (System.IO.Directory.Exists(txtPath.Text.Trim()) == false)
{
System.IO.Directory.CreateDirectory(txtPath.Text.Trim());
}
SqlConnection con = new SqlConnection(txtConnString.Text.Trim());
SqlCommand cmd = new SqlCommand("EXEC sp_ScriptTable " + TableName, con);
cmd.CommandType = CommandType.Text;
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
string path = txtPath.Text.Trim() + TableName + ".sql";
FileStream fs = new FileStream(txtPath.Text.Trim() + TableName + ".sql", FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter sw = new StreamWriter(fs, Encoding.Default);
while (dr.Read())
{
sw.WriteLine(dr[0].ToString());
}
sw.Close();
fs.Close();
}
上述方法使用 SP_ScriptTable 生成创建表定义脚本。 在你的 SQL Server 数据库中运行以下 SQL Server 脚本。 此脚本仅在 SQL Server 2005 及更高版本中运行。
CREATE PROCEDURE sp_ScriptTable
(
@TableName SYSNAME,
@IncludeConstraints BIT = 1,
@IncludeIndexes BIT = 1,
@NewTableName SYSNAME = NULL,
@UseSystemDataTypes BIT = 0
)
AS
BEGIN
DECLARE @MainDefinition TABLE
(
FieldValue VARCHAR(200)
)
DECLARE @DBName SYSNAME
DECLARE @ClusteredPK BIT
DECLARE @TableSchema NVARCHAR(255)
SET @DBName = DB_NAME(DB_ID())
SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)
DECLARE @ShowFields TABLE
(
FieldID INT IDENTITY(1,1),
DatabaseName VARCHAR(100),
TableOwner VARCHAR(100),
TableName VARCHAR(100),
FieldName VARCHAR(100),
ColumnPosition INT,
ColumnDefaultValue VARCHAR(100),
ColumnDefaultName VARCHAR(100),
IsNullable BIT,
DataType VARCHAR(100),
MaxLength INT,
NumericPrecision INT,
NumericScale INT,
DomainName VARCHAR(100),
FieldListingName VARCHAR(110),
FieldDefinition CHAR(1),
IdentityColumn BIT,
IdentitySeed INT,
IdentityIncrement INT,
IsCharColumn BIT
)
DECLARE @HoldingArea TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @PKObjectID TABLE
(
ObjectID INT
)
DECLARE @Uniques TABLE
(
ObjectID INT
)
DECLARE @HoldingAreaValues TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @Definition TABLE
(
DefinitionID SMALLINT IDENTITY(1,1),
FieldValue VARCHAR(200)
)
INSERT INTO @ShowFields
(
DatabaseName,
TableOwner,
TableName,
FieldName,
ColumnPosition,
ColumnDefaultValue,
ColumnDefaultName,
IsNullable,
DataType,
MaxLength,
NumericPrecision,
NumericScale,
DomainName,
FieldListingName,
FieldDefinition,
IdentityColumn,
IdentitySeed,
IdentityIncrement,
IsCharColumn
)
SELECT
DB_NAME(),
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CAST(ORDINAL_POSITION AS INT),
COLUMN_DEFAULT,
dobj.name AS ColumnDefaultName,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
DATA_TYPE,
CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
CAST(NUMERIC_PRECISION AS INT),
CAST(NUMERIC_SCALE AS INT),
DOMAIN_NAME,
COLUMN_NAME + ',','' AS FieldDefinition,
CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
FROM
INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
WHERE c.TABLE_NAME = @TableName
ORDER BY
c.TABLE_NAME, c.ORDINAL_POSITION
SELECT TOP 1 @TableSchema = TableOwner
FROM @ShowFields
INSERT INTO @HoldingArea (Flds) VALUES('(')
INSERT INTO @Definition(FieldValue)
VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)
INSERT INTO @Definition(FieldValue)
VALUES('(')
INSERT INTO @Definition(FieldValue)
SELECT
CHAR(10) + FieldName + ' ' +
CASE
WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN
DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
ELSE UPPER(DataType) +
CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
CASE WHEN IdentityColumn = 1 THEN
' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN
'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
END +
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
FROM @ShowFields
IF @IncludeConstraints = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
FROM
(
SELECT
ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,
REVERSE(SUBSTRING(REVERSE((
SELECT cp.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((
SELECT cr.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk
) a
WHERE ParentObject = @TableName
INSERT INTO @Definition(FieldValue)
SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = @TableName
INSERT INTO @PKObjectID(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 1 AND
is_primary_key = 1
INSERT INTO @Uniques(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 2 AND
is_primary_key = 0 AND
is_unique_constraint = 1
SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
INSERT INTO @Definition(FieldValue)
SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN
' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END
WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
REVERSE(SUBSTRING(REVERSE((
SELECT
c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
i.object_id = ccok.parent_object_id AND
ccok.object_id = cco.object_id
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM
sys.key_constraints cco
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN @Uniques u ON cco.object_id = u.objectID
WHERE
OBJECT_NAME(cco.parent_object_id) = @TableName
END
INSERT INTO @Definition(FieldValue)
VALUES(')')
IF @IncludeIndexes = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' +
REVERSE(SUBSTRING(REVERSE((
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE
OBJECT_NAME(sc.object_id) = @TableName AND
sc.object_id = i.object_id AND
sc.index_id = i.index_id
ORDER BY index_column_id ASC
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM sys.indexes i
WHERE
OBJECT_NAME(object_id) = @TableName
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
AND is_unique_constraint = 0
AND is_primary_key = 0
END
INSERT INTO @MainDefinition(FieldValue)
SELECT FieldValue FROM @Definition
ORDER BY DefinitionID ASC
SELECT * FROM @MainDefinition
END
说明
我使用了两个类：FileStream 类和 Stream 类。FileStream 类是从 Stream 类派生的。 此类用于从文件读取和写入，例如字节、字符、字符串和其他数据类型值到文件。 我使用的另一个类是 StreamWriter。 StreamWriter 类的 Write 和 WriteLine 成员写入文件。
在上面的代码中，我使用了 FileStream 类来创建一个 sql 文件，并使用 StreamWrite 将文本写入 sql 文件。
在上面的代码中，你可以看到定义了两个名为 GenerateStoredProc 和 GenerateTableScript 的方法。在第一个方法中，我使用了 SP_HELPTEXT 命令来生成存储过程脚本，在第二个方法中，我调用了一个存储过程来生成创建表脚本，并将其保存到用户提供的路径中。