我正在通过运行此查询来备份所有视图,并将结果存储在表中:
select
TABLE_CATALOG as DBName
, TABLE_NAME as ViewName
, VIEW_DEFINITION as ViewDef
, datalength(VIEW_DEFINITION) as [Length]
, GETDATE() as ImportDate
from INFORMATION_SCHEMA.VIEWS
order by DBName, ViewName
但是,view_definition列的数据类型设置为nvarchar(4000(,我的某些视图比这更长 - 因此它们截断了。
information_schema.views中的列
我可以将View_definition列的数据类型更改为Varchar(Max(以某种方式?
因为Information_schema.Views是您可以运行EXEC sp_helptext 'information_schema.views'
的视图以查找定义。这返回
CREATE VIEW INFORMATION_SCHEMA.VIEWS
AS
SELECT DB_NAME() AS TABLE_CATALOG ,
SCHEMA_NAME(schema_id) AS TABLE_SCHEMA ,
name AS TABLE_NAME ,
CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(object_id)) AS VIEW_DEFINITION ,
CONVERT(VARCHAR(7) ,
CASE with_check_option
WHEN 1 THEN 'CASCADE'
ELSE 'NONE'
END) AS CHECK_OPTION ,
'NO' AS IS_UPDATABLE
FROM sys.views;
从那里编辑以获取所需的东西
SELECT
DB_NAME() AS DBName,
name AS ViewName,
OBJECT_DEFINITION(object_id) AS ViewDef,
LEN(OBJECT_DEFINITION(object_id)) AS [Length],
GETDATE() AS ImportDate
FROM
sys.views