22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
CREATE TABLE DataType(ID INT,Name VARCHAR(50));
GO
DECLARE @ID INT=10,@Name VARCHAR(100)=REPLICATE('A',100),@ErrorMsg NVARCHAR(200)
;WITH CTECol(ColType,Lenth ,ColName)
AS
(
SELECT CAST(SQL_VARIANT_PROPERTY(@ID,'BaseType')AS sysname),CAST(SQL_VARIANT_PROPERTY(@ID,'MaxLength') AS INT),CAST('ID' AS sysname)
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(@Name,'BaseType') AS sysname),CAST(SQL_VARIANT_PROPERTY(@Name,'MaxLength') AS INT),'Name'
)
SELECT @ErrorMsg=STUFF(Col,1,1,'')
FROM (
SELECT ','+CASE WHEN d.ColType<>c.name THEN d.ColType+'|'+c.name+'类型不一致' ELSE '' END+CASE WHEN d.Lenth>b.max_length THEN RTRIM(d.Lenth)+'|'+RTRIM(b.max_length)+'长度不兼容' ELSE '' END
FROM sys.tables AS a
INNER JOIN sys.columns AS b ON b.object_id = a.object_id
INNER JOIN sys.types AS c ON c.user_type_id = b.user_type_id
INNER JOIN CTECol AS d ON d.ColName=b.name AND (d.ColType<>c.name OR d.Lenth>b.max_length)
WHERE a.name='DataType'
FOR XML PATH(''))t(Col)
IF @ErrorMsg>''
RAISERROR(@ErrorMsg,16,1)
/*
消息 50000,级别 16,状态 1,第 23 行
100|50长度不兼容
*/
SELECT t.name As table_name,
c.column_id,
c.name AS column_name,
p.name As type_name,
c.max_length,
c.precision,
c.scale
FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
JOIN sys.types p
ON c.system_type_id = p.system_type_id
WHERE type='U'
AND t.name = 'table1'
ORDER BY table_name, column_id