会python真的可以为所欲为0(回)
332天前
这里还有人吗1(回)
517天前这里还有人吗0(回)
517天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
742天前到处都是羊，不想上班0(回)
822天前
鸽子
0(回)
826天前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() |
*/ |
by: 发表于：2017-09-08 09:56:08 顶(0) | 踩(0) 回复
？？
回复评论