用户定义的表类型定义 [英] User-defined table type definition
本文介绍了用户定义的表类型定义的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想创建一个使用T-SQL克隆SQL Server数据库的过程.我正在使用游标克隆对象(表,过程,函数等).
I want to create a procedure to clone SQL Server database using T-SQL . I'm using cursors to clone objects (tables, procedures, functions ,...).
如何从系统目录视图中获取用户定义的表类型定义?
How can i get user-defined-table-type definition from System Catalog Views ?
推荐答案
这是您的答案.下面的查询以文本形式给出所有类型的结果.您可以使用循环或其他方式处理此结果,然后迁移到另一个数据库.
Here is your answer. Below query gives the result of all types as a text. You can proceed this result using loop or whatever and migrate to another database.
SELECT '
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N''' + st.[name] + ''' AND ss.name = N''' + ss.[name] + ''')
DROP TYPE ' + QUOTENAME(ss.name, '[') + '.' + QUOTENAME(st.name, '[') + '
GO
CREATE TYPE ' + QUOTENAME(ss.name, '[') + '.' + QUOTENAME(st.name, '[') + ' FROM ' +
QUOTENAME(bs.[name], '[') +
CASE bs.[name]
WHEN 'char' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length) + ')' END)
WHEN 'nchar' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length/2) + ')' END)
WHEN 'varchar' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length) + ')' END)
WHEN 'nvarchar' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length/2) + ')' END)
WHEN 'numeric' THEN (CASE ISNULL(st.[precision], 0) WHEN 0 THEN '' ELSE '(' + convert(varchar(10), st.[precision]) + ', ' + convert(varchar(10), st.[scale]) + ')' END)
WHEN 'decimal' THEN (CASE ISNULL(st.[precision], 0) WHEN 0 THEN '' ELSE '(' + convert(varchar(10), st.[precision]) + ', ' + convert(varchar(10), st.[scale]) + ')' END)
WHEN 'varbinary' THEN (CASE st.max_length WHEN -1 THEN '(max)' ELSE '(' + convert(varchar(10), st.max_length) + ')' END)
ELSE ''
END +
'
GO
'
FROM sys.types st
INNER JOIN sys.schemas ss ON st.[schema_id] = ss.[schema_id]
INNER JOIN sys.types bs ON bs.[user_type_id] = st.[user_type_id]
WHERE st.[is_user_defined] = 1
ORDER BY st.[name], ss.[name]
这篇关于用户定义的表类型定义的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文