--获取某个表插入时必须要的字段
DECLARE @tableName NVARCHAR(200)
SET @tableName='BuyApply' --替换实际表名
;WITH cte AS (
SELECT c.name AS colName FROM sys.columns c
WHERE c.[object_id]=OBJECT_ID(@tableName)
AND c.is_nullable=0 --不可为空
AND c.is_identity=0 --不是标识列
AND c.is_computed=0 --不是计算列
AND NOT EXISTS( --不存在默认约束
SELECT * FROM sys.default_constraints AS s
WHERE s.parent_object_id=c.[object_id]
AND s.parent_column_id=c.column_id
)
)
SELECT 'INSERT INTO dbo.'+@tableName+'('+(
STUFF( (SELECT ','+colName FROM cte FOR XML PATH('')),1,1,'' )
+') VALUES('+
STUFF( (SELECT ',@'+colName FROM cte FOR XML PATH('')),1,1,'' )
)+')'
AS insertSQL
获取某个表的写插入语句时最少需要的列名脚本
最新推荐文章于 2022-09-26 08:52:19 发布