MSSQL DBA 精华总结[处理对象名称中特殊字符]

Cherish_j_wang

MSSQL DBA 精华总结

之查询使用了某个关键字的对象

背景

在MSSQL DBA平时的工作中，经常会遇到下面的Case：

1.        请帮忙查看使用到表（或者对象）XXX的数据库对象有哪些？

2.        请帮忙查看UPDATE/DELETE/INSERT表(或者对象)XXX的数据库对象有哪些？

3.        查看有哪些对象引用到对象XXX？

可能开始的时候，我们遇到这样需求的时候，会不知所措，使用下面的脚本，可以很轻松的解决这个问题。

脚本

1. use master
   
2. GO
   
3. SET NOCOUNT ON
   
4. 
   
5. --===========public variables
   
6. declare
   
7.         @database_name sysname
   
8.         , @object_type sysname
   
9.         , @Search_key nvarchar(2000)
   
10. ;
    
11. select
    
12.         @database_name        = 'test'                        -- NULL:all databases;
    
13.         , @object_type        = 'V'                                -- NULL:all object_name types;P=proc,V=view,FN/TF/IF=Function
    
14.         , @Search_key = N'Test'     -- the search content you wanted
    
15. ;
    
16.        
    
17. 
    
18. IF object_ID('tempdb.dbo.#temp','u') IS NOT NULL
    
19.         DROP TABLE #temp
    
20. 
    
21. CREATE TABLE #temp(
    
22.         database_name        sysname NULl,
    
23.         schema_name                sysname NULL,
    
24.         object_name                sysname NULL,
    
25.         object_type                sysname NULL,
    
26.         create_date                datetime NULL
    
27.         ,modify_date        datetime NULL
    
28. )
    
29. ;
    
30. 
    
31. --===========private variables
    
32. DECLARE
    
33.         @sql                nvarchar(2000)--because sp_msforeachdb support nvarchar(2000) only
    
34. ;
    
35. 
    
36. SELECT
    
37.         @sql = N'
    
38. USE [?]
    
39. -- USE [pubs] for example
    
40. RAISERROR(N''Search on database: ?'', 10, 1) WITH NOWAIT
    
41. INSERT #temp
    
42. SELECT DB_NAME()
    
43.                 , schema_name(o.schema_id)
    
44.                 , O.name
    
45.                 , O.type
    
46.                 , O.create_date
    
47.                 , O.modify_date
    
48. FROM sys.objects O WITH(NOLOCK)
    
49.         INNER JOIN sys.sql_modules C WITH(NOLOCK)
    
50.         ON O.object_id = C.object_id
    
51. WHERE
    
52.         O.type IN (''P'',''V'',''FN'',''TF'',''IF'')
    
53.         AND C.definition LIKE N''%' + @Search_key + N'%'''
    
54. 
    
55. --for each database search
    
56. EXEC sys.sp_msforeachdb @sql
    
57. 
    
58. --show the resule
    
59. SELECT DISTINCT
    
60.                 database_name
    
61.                 , schema_name
    
62.                 , object_name
    
63.                 , object_type = CASE object_type
    
64.                                                         WHEN N'P' THEN 'Procedure'
    
65.                                                         WHEN N'V' THEN 'View'
    
66.                                                         ELSE 'User Function'
    
67.                                                 END
    
68.                 , create_date
    
69.                 , modify_date
    
70.                 , sp_helptext = database_name + '.' + schema_name + '.sp_helptext ' + object_name
    
71. FROM #temp
    
72. WHERE
    
73.         database_name = ISNULL(@database_name,database_name)
    
74.         AND object_type = ISNULL(@object_type,object_type)
    
75. ORDER BY database_name, schema_name, object_name

复制代码

我的测试结果
database_name        schema_name        object_name        object_type        create_date        modify_date        sp_helptext
test        dbo        UV_Test        View        2012-03-27 20:50:39.727        2012-03-27 20:50:39.727        test.dbo.sp_helptext UV_Test

saintdragon

支持原创分享

如此难忘

很不错 谢谢分享

hishale

真的很不错，，

cncxy259

好贴~~~留个mark，，细细品尝！！楼主辛苦！

Cherish_j_wang

好久没有来弄这个帖子了，也不知道大家希望获取哪个方面的信息，现在不知道要为大家分享什么东西了~

Cherish_j_wang

MSSQL DBA精华总结

之查看对象引用的关系

先把附件放到这里

FindTableTypeReferences.sql (51.05 KB, 下载次数: 55)

2012-4-7 21:54 上传

点击文件名下载附件

背景：

今天在做MSSQL Server 2008 Value Table Parameter测试的时候，遇到一个问题，在表值类型对象被引用以后，我们再想要删除、修改这个表值类型，就会报告错误，错误信息如下：

Msg 3732, Level 16, State 1, Line 5Cannot drop type 'testTableType' because it is being referenced by object 'Up_testTableParameter'. There may be other objects that reference this type.   

那么，我们的问题是，如果在生产系统中，如果我们创建了很多的用户定义(表)类型，当我们需要去修改或者删除这些类型的时候，我们如何去找出这些引用到他们的对象呢？这个就是我们今天要解决的问题：   这个错误出现在执行下面的脚本：

1. --table value parameter
   
2. use test
   
3. go
   
4. if exists(
   
5.                 select * from sys.types
   
6.                 where name = 'testTableType'
   
7.                 )
   
8.         drop type testTableType
   
9. GO
   
10. create type testTableType as table
    
11. (
    
12.         rowid int not null primary key
    
13.         ,name varchar(20) null
    
14.         ,city varchar(20) null
    
15. );
    
16. GO
    
17. 
    
18. --create test procedure
    
19. if OBJECT_ID('dbo.Up_testTableParameter','p') is not null
    
20.         drop proc dbo.Up_testTableParameter
    
21. GO
    
22. 
    
23. create proc dbo.Up_testTableParameter
    
24. (
    
25.         @tableParameter testTableType READONLY
    
26. )
    
27. AS
    
28. BEGIN
    
29.         select *
    
30.         from @tableParameter;
    
31. END
    
32. GO
    
33. 
    
34. --test drop table value type
    
35.         drop type testTableType

复制代码

解决方法：

   请使用附件中的脚本来查看对象引用关系

我的测试结果结果如下：由于表格列太多，只贴出了部分列

object_id	object_name	object_schema	object_db
257	testTableType	dbo	test
407672500	Up_testTableParameter	dbo	test

…

ogrishman

楼主讲讲关于索引维护方面的吧

路人ABCD

有无邮件发送设置，实现查看sql 作业是否错误之类的方法？

hwtong

路人ABCD 发表于 2012-4-23 00:09
有无邮件发送设置，实现查看sql 作业是否错误之类的方法？

你是指在Job运行结束之后,发出邮件吗?
可以选择到运行结束发送邮件,运行成功发送邮件,运行失败发送邮件.
可以参考
http://space.itpub.net/16754036/viewspace-700263