数据库对象关系研究，数据库中的表、视图、存储过程和用户定义函数之间的关系

[sql]代码库

USE [DatabaseAnalysis]
GO

/*
--SQL Server数据库对象表
CREATE TABLE [dbo].[SqlObject](
	[sqlObjectType] [varchar](20) NOT NULL,
	[sqlObjectName] [sysname] NOT NULL,
	[updated] [bit] NOT NULL
 CONSTRAINT [PK_SqlObject] PRIMARY KEY CLUSTERED 
(
	[sqlObjectName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--SQL Server数据库对象关系表
CREATE TABLE [dbo].[SqlObjectRelation](
	[subSqlObjectType] [varchar](20) NOT NULL,
	[subSqlObjectName] [sysname] NOT NULL,
	[sqlObjectType] [varchar](20) NOT NULL,
	[sqlObjectName] [sysname] NOT NULL,
	[folderOrPath] [nvarchar](1000) NOT NULL,
	[updated] [bit] NOT NULL,
 CONSTRAINT [PK_SqlObjectRelation] PRIMARY KEY NONCLUSTERED 
(
	[subSqlObjectName] ASC,
	[sqlObjectName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--SQL Server数据库对象名称包含关系表
CREATE TABLE [dbo].[SqlSubStringObject](
	[sqlSubStringObjectName] [sysname] NOT NULL,
	[sqlObjectName] [sysname] NOT NULL,
	[updated] [bit] NOT NULL,
 CONSTRAINT [PK_SqlSubStringObject] PRIMARY KEY CLUSTERED 
(
	[sqlSubStringObjectName] ASC,
	[sqlObjectName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--SQL Server数据库现有对象关系视图
CREATE VIEW [dbo].[V_SqlObjectRelation]
AS
SELECT [subSqlObjectType],
	[subSqlObjectName],
	[sqlObjectType],
	[sqlObjectName],
	[folderOrPath]
FROM [dbo].[SqlObjectRelation]
WHERE [updated] = 1

GO
*/
--获取数据库中所有的表、视图、存储过程和用户定义函数，更新数据库对象表
UPDATE [dbo].[SqlObject]
SET [updated] = 0
WHERE [updated] = 1;

MERGE INTO [dbo].[SqlObject] AS T
USING (
	SELECT [TABLE_TYPE] [sqlObjectType],
		[TABLE_NAME] [sqlObjectName]
	FROM [BugNET].INFORMATION_SCHEMA.TABLES
	
	UNION ALL
	
	SELECT 'StoredProcedure',
		sp.NAME
	FROM [BugNET].sys.all_objects AS sp
	WHERE (
			(
				sp.type = N'P'
				OR sp.type = N'RF'
				OR sp.type = N'PC'
				)
			AND (
				CAST(CASE 
						WHEN sp.is_ms_shipped = 1
							THEN 1
						WHEN (
								SELECT major_id
								FROM [BugNET].sys.extended_properties
								WHERE major_id = sp.object_id
									AND minor_id = 0
									AND class = 1
									AND NAME = N'microsoft_database_tools_support'
								) IS NOT NULL
							THEN 1
						ELSE 0
						END AS BIT) = 0
				)
			)
	
	UNION ALL
	
	SELECT 'UserDefinedFunction',
		udf.NAME
	FROM [BugNET].sys.all_objects AS udf
	WHERE (
			udf.type IN (
				'TF',
				'FN',
				'IF',
				'FS',
				'FT'
				)
			)
		AND CAST(CASE 
				WHEN udf.is_ms_shipped = 1
					THEN 1
				WHEN (
						SELECT major_id
						FROM sys.extended_properties
						WHERE major_id = udf.object_id
							AND minor_id = 0
							AND class = 1
							AND NAME = N'microsoft_database_tools_support'
						) IS NOT NULL
					THEN 1
				ELSE 0
				END AS BIT) = 0
	) AS S
	ON S.[sqlObjectName] = T.[sqlObjectName]
WHEN MATCHED
	THEN
		UPDATE
		SET T.[sqlObjectType] = S.[sqlObjectType],
			T.[updated] = 1
WHEN NOT MATCHED
	THEN
		INSERT
		VALUES (
			[sqlObjectType],
			[sqlObjectName],
			1
			);

--获取数据库对象名称之间包含关系，更新数据库对象名称包含关系表
UPDATE [dbo].[SqlSubStringObject]
SET [updated] = 0
WHERE [updated] = 1;

MERGE INTO [dbo].[SqlSubStringObject] T
USING (
	SELECT S2.[sqlObjectName] [sqlSubStringObjectName],
		S1.[sqlObjectName]
	FROM [DatabaseAnalysis].[dbo].[SqlObject] S1
	INNER JOIN [DatabaseAnalysis].[dbo].[SqlObject] S2 ON S1.[sqlObjectName] LIKE '%' + S2.[sqlObjectName] + '%'
		AND S1.[updated] = 1
		AND S2.[updated] = 1
		AND S1.[sqlObjectName] <> S2.[sqlObjectName]
	) S
	ON T.[sqlSubStringObjectName] = S.[sqlSubStringObjectName]
		AND T.[sqlObjectName] = S.[sqlObjectName]
WHEN MATCHED
	THEN
		UPDATE
		SET T.[updated] = 1
WHEN NOT MATCHED
	THEN
		INSERT
		VALUES (
			S.[sqlSubStringObjectName],
			S.[sqlObjectName],
			1
			);

UPDATE [dbo].[SqlObjectRelation]
SET [updated] = 0
WHERE [updated] = 1;

SELECT *
INTO #SqlObjectRelation
FROM (
	SELECT t.[sqlObjectType] [subSqlObjectType],
		t.[sqlObjectName] [subSqlObjectName],
		v.[sqlObjectType],
		v.[sqlObjectName],
		@@SERVERNAME + '\' + v.[sqlDatabaseName] + '\' + v.[sqlObjectSchema] + '.' + v.[sqlObjectName] [folderOrPath],
		[sqlObjectDefinition]
	FROM [dbo].[SqlObject] t
	INNER JOIN (
		SELECT 'VIEW' [sqlObjectType],
			[TABLE_NAME] [sqlObjectName],
			[TABLE_CATALOG] [sqlDatabaseName],
			[TABLE_SCHEMA] [sqlObjectSchema],
			[VIEW_DEFINITION] [sqlObjectDefinition]
		FROM [BugNET].INFORMATION_SCHEMA.VIEWS
		
		UNION ALL
		
		SELECT 'StoredProcedure',
			sp.NAME,
			'BugNET',
			SCHEMA_NAME(sp.schema_id),
			c.TEXT
		FROM [BugNET].sys.all_objects AS sp
		INNER JOIN [BugNET].dbo.syscomments c ON c.id = sp.object_id
			AND CASE 
				WHEN c.number > 1
					THEN c.number
				ELSE 0
				END = 0
		WHERE (
				sp.type = N'P'
				OR sp.type = N'RF'
				OR sp.type = N'PC'
				)
			AND (
				CAST(CASE 
						WHEN sp.is_ms_shipped = 1
							THEN 1
						WHEN (
								SELECT major_id
								FROM [BugNET].sys.extended_properties
								WHERE major_id = sp.object_id
									AND minor_id = 0
									AND class = 1
									AND NAME = N'microsoft_database_tools_support'
								) IS NOT NULL
							THEN 1
						ELSE 0
						END AS BIT) = 0
				)
		
		UNION ALL
		
		SELECT 'UserDefinedFunction',
			udf.NAME,
			'BugNET',
			SCHEMA_NAME(udf.schema_id),
			m.DEFINITION
		FROM [BugNET].[sys].[all_sql_modules] m
		INNER JOIN [BugNET].[sys].[all_objects] udf ON m.object_id = udf.object_id
		WHERE DEFINITION LIKE '%create function%'
			AND udf.type IN (
				'TF',
				'FN',
				'IF',
				'FS',
				'FT'
				)
			AND CAST(CASE 
					WHEN udf.is_ms_shipped = 1
						THEN 1
					WHEN (
							SELECT major_id
							FROM [BugNET].sys.extended_properties
							WHERE major_id = udf.object_id
								AND minor_id = 0
								AND class = 1
								AND NAME = N'microsoft_database_tools_support'
							) IS NOT NULL
						THEN 1
					ELSE 0
					END AS BIT) = 0
		) v ON v.[sqlObjectDefinition] LIKE '%' + t.[sqlObjectName] + '%'
		AND t.[updated] = 1
		AND t.[sqlObjectName] <> v.[sqlObjectName]
	) A

--更新数据库对象关系表
MERGE INTO [dbo].[SqlObjectRelation] T
USING (
	SELECT DISTINCT [subSqlObjectType],
		[subSqlObjectName],
		[sqlObjectType],
		[sqlObjectName],
		[folderOrPath]
	FROM #SqlObjectRelation
	) S
	ON S.[subSqlObjectName] = T.[subSqlObjectName]
		AND S.[sqlObjectName] = T.[sqlObjectName]
		AND S.[folderOrPath] = T.[folderOrPath]
WHEN MATCHED
	THEN
		UPDATE
		SET T.[subSqlObjectType] = S.[subSqlObjectType],
			T.[sqlObjectType] = S.[sqlObjectType],
			T.[updated] = 1
WHEN NOT MATCHED
	THEN
		INSERT
		VALUES (
			S.[subSqlObjectType],
			S.[subSqlObjectName],
			S.[sqlObjectType],
			S.[sqlObjectName],
			S.[folderOrPath],
			1
			);

DECLARE @updated BIT,
	@subSqlObjectName SYSNAME,
	@sqlObjectName SYSNAME,
	@folderOrPath NVARCHAR(1000)

DECLARE curSqlObjectRelation CURSOR
FOR
SELECT [updated],
	[subSqlObjectName],
	[sqlObjectName],
	[folderOrPath]
FROM [dbo].[SqlObjectRelation]
FOR UPDATE OF [updated]

DECLARE @count SMALLINT

SELECT @count = 1

OPEN curSqlObjectRelation

FETCH NEXT
FROM curSqlObjectRelation
INTO @updated,
	@subSqlObjectName,
	@sqlObjectName,
	@folderOrPath

WHILE (@@fetch_status <> - 1)
BEGIN
	IF (@@fetch_status <> - 2)
	BEGIN
		IF (
				@updated = 1
				AND EXISTS (
					SELECT TOP 1 1
					FROM [dbo].[SqlSubStringObject]
					WHERE [sqlSubStringObjectName] = @subSqlObjectName
					)
				)
		BEGIN
			DECLARE @sqlObjectDefinition NVARCHAR(4000),
				@sqlParentObjectName SYSNAME

			SET @sqlObjectDefinition = ''

			IF (@subSqlObjectName = 'BugNet_ApplicationLog')
				SET @subSqlObjectName = @subSqlObjectName

			WHILE @sqlObjectDefinition IS NOT NULL
			BEGIN
				SET @sqlParentObjectName = ''
				SET @sqlObjectDefinition = (
						SELECT MIN([sqlObjectDefinition])
						FROM #SqlObjectRelation s
						WHERE s.[subSqlObjectName] = @subSqlObjectName
							AND s.[sqlObjectName] = @sqlObjectName
							AND s.[folderOrPath] = @folderOrPath
							AND s.[sqlObjectDefinition] > @sqlObjectDefinition
						)

				IF (@sqlObjectDefinition IS NOT NULL)
				BEGIN
					DECLARE @sqlObjectDefinition1 NVARCHAR(4000) = @sqlObjectDefinition

					WHILE @sqlParentObjectName IS NOT NULL
					BEGIN
						SET @sqlParentObjectName = (
								SELECT MIN([sqlObjectName])
								FROM [dbo].[SqlSubStringObject]
								WHERE [sqlSubStringObjectName] = @subSqlObjectName
									AND [sqlObjectName] > @sqlParentObjectName
								)

						IF (@sqlParentObjectName IS NOT NULL)
							SET @sqlObjectDefinition1 = REPLACE(@sqlObjectDefinition1, @sqlParentObjectName, '')
					END
				END

				IF (CHARINDEX(@subSqlObjectName, @sqlObjectDefinition1) = 0)
					UPDATE [dbo].[SqlObjectRelation]
					SET [updated] = 0
					WHERE [subSqlObjectName] = @subSqlObjectName
						AND [sqlObjectName] = @sqlObjectName
						AND [folderOrPath] = @folderOrPath
			END
		END
	END

	FETCH NEXT
	FROM curSqlObjectRelation
	INTO @updated,
		@subSqlObjectName,
		@sqlObjectName,
		@folderOrPath

	SELECT @count = @count + 1
END

CLOSE curSqlObjectRelation

DEALLOCATE curSqlObjectRelation
GO

DROP TABLE #SqlObjectRelation;

--显示数据库现有对象关系视图
SELECT [subSqlObjectType],
	[subSqlObjectName],
	[sqlObjectType],
	[sqlObjectName],
	[folderOrPath]
FROM [DatabaseAnalysis].[dbo].[V_SqlObjectRelation]
	/*
--开启clr enabled 选项
EXEC sp_configure N'clr enabled', N'1'
RECONFIGURE WITH OVERRIDE 

CREATE FUNCTION dbo.GetPackageContent ()
RETURNS NVARCHAR(4000)
AS
---sql c# clr 程序集文件名.类名.方法名
EXTERNAL NAME GetContentFromSSISPackage.UserDefinedFunctions.GetPackageContent
SELECT dbo.GetPackageContent()

*/