select * from sys.columns where object_id=object_id('vname')
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
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
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
另外,你有说是多个视图?
目前没有为变量 '@local_cursor' 分配游标。
消息 16950,级别 16,状态 2,过程 sp_MSforeach_worker,第 32 行
目前没有为变量 '@local_cursor' 分配游标。
消息 16950,级别 16,状态 2,过程 sp_MSforeach_worker,第 153 行
目前没有为变量 '@local_cursor' 分配游标。
消息 16916,级别 16,状态 1,过程 sp_MSforeach_worker,第 155 行
名为 'hCForEachDatabase' 的游标不存在。
视图是由SQL查询组成的,正如你写的那样,可能有各种连接,各个子句都可能有子查询,它是没办法解析到一个个规定的具体对象上的,因此,系统表中不可能记录除视图名/视图查询列等这些最基本信息外的其他信息了.甚至连它查询是基于什么表也不能规范地列出来,因为,假设你是把一个由数个表连接而获得数据集的子查询作为视图数据源的话,你让它怎么表述?
因此,对视图的分析还得从视图查询语句着手去考虑问题.
--表名=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
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