Sql Server 批量导出索引、存储过程、视图和函数

批量导出索引的代码：

WITH indexInfo as (
    SELECT SCHEMA_NAME(t.schema_id) [schema_name],t.name as [table_name],t1.name as [index_name]
    ,t1.type,t1.type_desc,t1.is_unique,t1.is_primary_key,t1.is_unique_constraint,t1.has_filter,t1.filter_definition
    ,STUFF((SELECT ','+t4.name FROM sys.sysindexkeys t2 
        inner join sys.index_columns  t3 ON t2.id=t3.object_id and t2.indid=t3.index_id and t2.colid=t3.column_id
        inner join sys.syscolumns t4 ON t2.id=t4.id and t2.colid=t4.colid
        WHERE t2.id=t1.object_id and t1.index_id=t2.indid  and t2.keyno <> 0 ORDER BY t3.key_ordinal FOR XML PATH('')),1,1,'') AS index_cols
    ,STUFF((SELECT ','+t4.name FROM sys.sysindexkeys t2 
        inner join sys.index_columns  t3 ON t2.id=t3.object_id and t2.indid=t3.index_id and t2.colid=t3.column_id
        inner join sys.syscolumns t4 ON t2.id=t4.id and t2.colid=t4.colid
        WHERE t2.id=t1.object_id and t1.index_id=t2.indid  and t2.keyno = 0 ORDER BY t3.key_ordinal FOR XML PATH('')),1,1,'')  AS include_cols
    FROM sys.tables as t
    inner join sys.indexes as t1 on (t1.index_id > 0 and t1.is_hypothetical = 0) and (t1.object_id=t.object_id)
    WHERE t1.type in(1,2)
), indexInfo2 AS (
SELECT * ,(CASE 
    WHEN is_primary_key = 1 
        THEN 'alter table '+[schema_name]+'.'+[table_name]+' add constraint '+[index_name]+' primary key '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+'('+index_cols+')'
    WHEN is_unique = 1 AND is_unique_constraint = 1 
        THEN 'alter table '+[schema_name]+'.'+[table_name]+' add constraint '+[index_name]+' unique '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+'('+index_cols+')'
    WHEN is_unique = 1 AND (is_primary_key = 0 OR is_unique_constraint = 0)
        THEN 'create unique '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+' index '+[index_name]+'  on '+[schema_name]+'.'+[table_name]+'('+index_cols+')'
    ELSE 'create '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+' index '+[index_name]+' on '+[schema_name]+'.'+[table_name]+'('+index_cols+') '
    END) script
FROM indexInfo
) SELECT [schema_name],[table_name],[index_name],script
+(CASE WHEN include_cols IS NOT NULL THEN ' include('+include_cols+')' ELSE '' END)
+(CASE WHEN has_filter = 1THEN ' where '+filter_definition ELSE '' END)
FROM indexInfo2
ORDER BY [schema_name],[table_name],[type],[index_name],is_primary_key DESC,is_unique_constraint DESC,is_unique DESC
GO

---

 

批量导出存储过程、函数和视图：

use ReportServer$SQLSERVER
 
select a.name,a.[type],b.[definition] from sys.all_objects a,sys.sql_modules b 
where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('P','V','AF') 
order by a.[name] asc

---

前一段时间，有个需求，就是对部分表进行了分库，所以，原库里面的存储过程、视图和函数里的表名等信息也要跟着更新，刚开始尝试手动检查了几个存储过程，可发现存储过程太多，检查起来效率很低，还容易出错，况且还有视图和函数，所以就想到了  Sql Server 内置的目录视图，找了一下，果然找到了解决办法：

 

use ReportServer$SQLSERVER
 
select a.name,a.[type],b.[definition] from sys.all_objects a,sys.sql_modules b 
where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('P','V','AF') 
order by a.[name] asc

 

从上面的SQL语句可以看出，主要用到了两个 sys.all_objects 和 sys.sql_modules 两个系统存储过程，其中 sys.all_objects 是 sql server 2012 版本中的系统视图，在 更早期的 sql server 版本中，应该用 sys.objects，同时，sys.objects 在 2012 中也是可以用的，只不过考虑到后续兼容性，在新版本中，用新的 name 还是比较好。

 

Sys.All_Objects(sys.objects) 

 

该视图 很出名，是经常使用到的，主要是：显示所有架构范围内的用户定义对象和系统对象的 UNION

 

主要字段：

 

1. Name：对象名

2. Object_id：对象标识号，在数据中是唯一的

3. Principal_id ：架构所有者ID

4. Parent_object_id：此对象所属对象的ID，0 = 不是子对象

5. Type：对象类型，常用的类型有, AF = 聚合函数 P = SQL 存储过程  V = 视图  TT = 表类型   U = 表（用户定义类型）

6. Type_desc：对象类型的说明

7. Create_date / Modify_date ：创建日期 / 修改日期

8. is_ms_shipped：是否为 内部 SQL Server 组建所创建的对象，常用来判断 是否是 系统内置或用户自定义 的对象

 

Sys.Sql_Modules

 

MSDN ： 对每个 SQL 语言定义的模块对象都返回一行。 类型为 P、RF、V、TR、FN、IF、TF 和 R 的对象均有关联的 SQL 模块。 

该视图 不是太常用，但是要返回 某些对象的 创建信息，如一个表格的 架构、字段等信息，就需要用这视图了，稍后会在介绍字段后再介绍这个视图。

注意，该视图的作用域是当前 DB，并不是当前Server，所以使用前，一定注意前面加 use DBName 的信息

 

主要字段：

 

1. Object_id：对象标识号，在数据中是唯一的

2. Definition：用于定义此模块的 SQL 文本

 

其实只要大家亲自操作下，就很容易明白，Definition 中的内容，就和下图中的操作是一样的：

 

本文首发博客园，原文地址：Sql Server 查看所有存储过程或视图的位置及内容 - http://www.cnblogs.com/xunziji/archive/2012/08/22/2650822.html

相关文章：

利用 sys.dm_exec_query_stats 查找并优化SQL语句

利用 sys.sysprocesses 检查 Sql Server的阻塞和死锁

Sql Server 系统表分析(1) - 备份表

Sql Server 系统表分析 （2） - 作业(job) 表

Sql Server 系统存储过程分析 1 - 目录存储过程