GetSchema方法获取数据库架构。这里提供一种免费比较数据库的方法。
下面是我编写的 SQL Server 脚本，它可以将数据库中存储过程、视图和表的内容输出到输出窗口。
通过调用以下命令来运行它：
exec [dbo].[ScriptStoredProcedures]
在我的许多项目中，我会运行此脚本，将文本复制到Visual Studio项目中的文件中，以便我可以检查我们的数据库在特定时间的外观副本。
（是的，您也可以在Visual Studio中拥有Database Projects，但这是一种替代方法。）
如果您在两个数据库上运行此脚本，则可以比较两个输出以查找差异。
CREATE PROCEDURE [dbo].[ScriptStoredProcedures]
AS
BEGIN
--
-- Attempt to create a long SQL script, to Drop, then "CREATE PROCEDURE" on all SPs and "CREATE FUNCTION" on all Functions in this database.
--
-- You can then run this script on a "target" database, and it'll have the latest Stored Procedures & functions
-- created/updated on it.
--
-- exec [dbo].[ScriptStoredProcedures]
--
SET NOCOUNT ON
PRINT '--'
PRINT '-- SQL Script, generated by the [ScriptStoredProcedures] Stored Procedure.'
PRINT '-- Created on ' + convert(nvarchar, GetDate(), 106) + ' ' + convert(nvarchar, GetDate(), 108)
PRINT '--'
PRINT '-- This will create/update the Stored Procedures on this database, to bring them up-to-date with the SPs '
PRINT '-- from the database ''' + DB_NAME() + ''' on the server ''' + @@SERVERNAME + ''''
PRINT '--'
PRINT '--'
-- Create a temporary table, where each record contains one line of Stored Procedure/Function script
-- (i.e. If you have a Stored Procedure with 30 lines of script in it, we'll create 30 temporary records
-- to store it)
CREATE TABLE #tmp
(
[inx] INT IDENTITY(1, 1),
[text] nvarchar(4000)
)
DECLARE @StoredProcedureName NVARCHAR(200)
DECLARE @StoredProcedureType NVARCHAR(10)
DECLARE @ExecCommand NVARCHAR(200)
DECLARE @OneLineOfScript NVARCHAR(4000)
-- First, get a list of all Stored Procedures & Functions in this database
DECLARE cursorEachStoredProcedure CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT [name], -- Name of the Stored Procedure or Function
[type] -- This will contain "FN" if it's a Function, or "P" if it's a Stored Procedure
FROM sysobjects
WHERE (OBJECTPROPERTY(id, N'IsProcedure') = 1
OR OBJECTPROPERTY(id, N'IsTableFunction') = 1
OR OBJECTPROPERTY(id, N'IsScalarFunction') = 1
OR OBJECTPROPERTY(id, N'IsView') = 1)
AND [name] NOT LIKE 'sp_%'
AND [name] NOT LIKE 'fn_%'
ORDER BY [type] DESC, -- Sort by Stored Procedures first, then functions
[name] -- then show the list of SPs/Functions in name order
OPEN cursorEachStoredProcedure
FETCH NEXT FROM cursorEachStoredProcedure INTO @StoredProcedureName, @StoredProcedureType
-- For each Stored Procedure we've found in our database, create some script to delete the Stored Procedure
-- from the target database if it exists, then re-create it.
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT ''
IF (@StoredProcedureType = 'P')
BEGIN
PRINT 'PRINT ''Creating stored procedure: ''''' + @StoredProcedureName + ''''''''
PRINT ''
PRINT 'IF EXISTS(select Name from sysobjects where OBJECTPROPERTY(id, N''IsProcedure'') = 1 AND Name = ''' + @StoredProcedureName + ''')'
PRINT 'BEGIN'
PRINT ' DROP PROCEDURE [' + @StoredProcedureName + '] '
PRINT 'END'
END
ELSE
IF (@StoredProcedureType = 'V')
BEGIN
PRINT 'PRINT ''Creating view: ''''' + @StoredProcedureName + ''''''''
PRINT ''
PRINT 'IF EXISTS(select Name from sysobjects where OBJECTPROPERTY(id, N''IsView'') = 1 AND Name = ''' + @StoredProcedureName + ''')'
PRINT 'BEGIN'
PRINT ' DROP VIEW [' + @StoredProcedureName + '] '
PRINT 'END'
END
ELSE
BEGIN
PRINT 'PRINT ''Creating function: ''''' + @StoredProcedureName + ''''''''
PRINT ''
PRINT 'IF EXISTS(select Name from sysobjects where (OBJECTPROPERTY(id, N''IsTableFunction'') = 1 OR OBJECTPROPERTY(id, N''IsScalarFunction'') = 1) AND Name = ''' + @StoredProcedureName + ''')'
PRINT 'BEGIN'
PRINT ' DROP FUNCTION [' + @StoredProcedureName + '] '
PRINT 'END'
END
PRINT 'GO '
-- Run the "sp_helptext" command, to get the text of this Stored Procedure (one row per *line* of script)
-- and store this set of results in a temporary table, so we can step through, line-by-line, and send
-- the output to the Messages window.
SET @ExecCommand = 'sp_helptext @objname = ''' + @StoredProcedureName + ''''
DELETE FROM #tmp
INSERT INTO #tmp
EXEC(@ExecCommand)
-- Step through each line of this Stored Procedure
DECLARE cursorEachLineOfScript CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT [text]
FROM #tmp
ORDER BY [inx]
OPEN cursorEachLineOfScript
FETCH NEXT FROM cursorEachLineOfScript INTO @OneLineOfScript
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- For each line of Stored Procedure script, send the text to the Messages window
PRINT @OneLineOfScript
FETCH NEXT FROM cursorEachLineOfScript INTO @OneLineOfScript
END
CLOSE cursorEachLineOfScript
DEALLOCATE cursorEachLineOfScript
PRINT 'GO '
FETCH NEXT FROM cursorEachStoredProcedure INTO @StoredProcedureName, @StoredProcedureType
END
CLOSE cursorEachStoredProcedure
DEALLOCATE cursorEachStoredProcedure
DROP TABLE #tmp
PRINT 'EXEC [dbo].[spGrantExectoAllStoredProcs]'
PRINT 'GO'
PRINT '--'
PRINT '--'
PRINT '-- List of tables (and their fields) in this database'
PRINT '--'
PRINT '--'
PRINT '--'
-- First, let's iterate through our list of tables, and find out which fields they contain.
DECLARE
@tableName nvarchar(200),
@fieldName nvarchar(500),
@fieldType nvarchar(500),
@fieldNullable nvarchar(200)
DECLARE cursorTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT st.NAME as 'Table_name'
FROM sys.tables st
ORDER BY 1
OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '-- Table: ' + @tableName
DECLARE cursorFields CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT sc.NAME as 'Field_name',
case when t.Name in ('char', 'varchar', 'nvarchar')
then t.Name + '(' + cast(sc.max_length/2 as nvarchar) + ')'
else
case when t.Name in ('numeric')
then t.Name + '(' + cast(sc.precision as nvarchar) + ',' + cast(sc.scale as nvarchar) + ')'
else t.Name
end
end as 'Data_type',
case when sc.is_nullable=1 then 'null' else 'not null' end as 'Nullable'
FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
INNER JOIN sys.types t ON sc.system_type_id = t.system_type_id
WHERE t.Name != 'sysname'
AND st.name = @tableName
ORDER BY 1, 2
OPEN cursorFields
FETCH NEXT FROM cursorFields INTO @fieldName, @fieldType, @fieldNullable
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '-- ' + @fieldName + ' (' + @fieldType + ', ' + @fieldNullable + ')'
FETCH NEXT FROM cursorFields INTO @fieldName, @fieldType, @fieldNullable
END
CLOSE cursorFields
DEALLOCATE cursorFields
PRINT '--'
FETCH NEXT FROM cursorTables INTO @tableName
END
CLOSE cursorTables
DEALLOCATE cursorTables
END
INNER JOIN sys.types t ON sc.system_type_id = t.system_type_id会在输出中创建重复的列。看起来应该使用列sys.types.user_type_id，因为它对于标准类型具有与system_type_id相同的内容。 - FrankM有两个建议。