USE MASTER
GO
CREATE proc sp_MSforeachObject
@objectType int=1,
@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null,
@whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null,
@postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its
own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))
if (@precommand is not null)
exec(@precommand)
/* Defined @isobject for save object type */
Declare @isobject varchar(256)
select @isobject= case @objectType when 1 then 'IsUserTable'
when 2 then 'IsView'
when 3 then 'IsTrigger'
when 4 then 'IsProcedure'
when 5 then 'IsDefault'
when 6 then 'IsForeignKey'
when 7 then 'IsScalarFunction'
when 8 then 'IsInlineFunction'
when 9 then 'IsPrimaryKey'
when 10 then 'IsExtendedProc'
when 11 then 'IsReplProc'
when 12 then 'IsRule'
end
/* Create the select */
/* Use @isobject variable isstead of IsUserTable string */
EXEC(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' +
REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
+ N' where OBJECTPROPERTY(o.id, N'''+@isobject+''') = 1 '+N' and o.category & ' + @mscat + N' = 0 '
+ @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
return @retval
GO
这样我们来测试一下:
--获得所有的存储过程的脚本:
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=4
--获得所有的视图的脚本:
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=2
--比如在开发过程中,没一个用户都是自己的OBJECT OWNER,所以在真实的数据库时都要改为DBO:
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=1
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=2
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=3
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4
SELECT
TOP 100000 --表名=case when a.colorder=1 then d.name else '' end,
--表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
字段标识 = CASE
WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN
'√'
ELSE
''
END,
主键 = CASE
WHEN EXISTS (
SELECT
1
FROM
sysobjects
WHERE
xtype = 'PK'
AND name IN (
SELECT
name
FROM
sysindexes
WHERE
indid IN (
SELECT
indid
FROM
sysindexkeys
WHERE
id = a.id
AND colid = a.colid
)
)
) THEN
'√'
ELSE
''
END,
类型 = b.name,
占位数 = a.length,
长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
小数位 = isnull(
COLUMNPROPERTY(a.id, a.name, 'Scale'),
0
),
允许空 = CASE
WHEN a.isnullable = 1 THEN
'√'
ELSE
''
END,
默认值 = isnull(e. TEXT, ''),
字段描述 = isnull(g.[value], ''),
所属数据表 = '[' + h.TABLE_SCHEMA + '].' + d.name,
数据表描述 = isnull(f. VALUE, ''),
crdate AS [创建时间],
refdate AS [更改时间]
FROM
syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.xtype IN ('U', 'V')
AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
INNER JOIN (
SELECT
*
FROM
[INFORMATION_SCHEMA].[TABLES]
) h ON h.TABLE_NAME = d.name
WHERE
h.TABLE_SCHEMA != 'Ant' --d.name = '要查询的表' --如果只查询指定表,加上此条件
ORDER BY
[所属数据表],
a.colorder
