USE [AdventureWorks2012]
go
IF object_id('USP_GENERATEDML') IS NOT NULL
BEGIN
PRINT 'Dropping procedure USP_GENERATEDML'
DROP PROCEDURE [USP_GENERATEDML]
IF @@ERROR = 0 PRINT 'Procedure USP_GENERATEDML dropped'
END
go
CREATE PROCEDURE [USP_GENERATEDML]
@TBLNAME NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @result as TABLE (
[PREFIX] [varchar](1500) ,
[NAME] [nvarchar](2630) ,
[ENDFIX] [nvarchar](2800) ,
[STARTFLAG] [bigint] ,
[TABLE_SCHEMA] [nvarchar](2800) ,
[TABLE_NAME] [nvarchar](2800) ,
[FLAG] [varchar](600)
)
;
WITH cte AS (
SELECT ROW_NUMBER() OVER(
PARTITION BY t.TABLE_SCHEMA,
t.TABLE_NAME ORDER BY c.name ASC
) AS startflag, ROW_NUMBER() OVER(
PARTITION BY t.TABLE_SCHEMA,
t.TABLE_NAME ORDER BY c.name DESC
) AS endflag, QUOTENAME(t.TABLE_SCHEMA) AS TABLE_SCHEMA,QUOTENAME(t.TABLE_NAME) AS TABLE_NAME, QUOTENAME(c.name) AS name
FROM INFORMATION_SCHEMA.TABLES AS t
INNER JOIN syscolumns c
ON id = OBJECT_ID( t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
WHERE t.TABLE_TYPE = 'BASE TABLE'
)
SELECT * INTO #COLHELP
FROM cte t
INSERT INTO @result
SELECT CASE T.STARTFLAG WHEN 1 THEN 'SELECT ' ELSE '' END AS PREFIX,
T.NAME,CASE T.ENDFLAG WHEN 1 THEN ' FROM '+t.TABLE_SCHEMA+'.'+t.TABLE_NAME ELSE ',' END AS ENDFIX,
T.STARTFLAG,t.TABLE_SCHEMA, t.TABLE_NAME,'SELECT' AS FLAG
FROM #COLHELP T
UNION
SELECT CASE T.STARTFLAG WHEN 1 THEN 'INSERT INTO '+t.TABLE_SCHEMA+'.'+t.TABLE_NAME+'( ' ELSE '' END AS PREFIX,
T.NAME,CASE T.ENDFLAG WHEN 1 THEN ' ) ' ELSE ',' END AS ENDFIX,
T.STARTFLAG,t.TABLE_SCHEMA, t.TABLE_NAME,'INSERT' AS FLAG
FROM #COLHELP T
UNION
SELECT CASE T.STARTFLAG WHEN 1 THEN 'VALUES ( ' ELSE '' END AS PREFIX,
'@'+SUBSTRING(T.NAME,2,LEN(T.NAME)-2) AS NAME ,CASE T.ENDFLAG WHEN 1 THEN ' ) ' ELSE ',' END AS ENDFIX,
T.STARTFLAG+5000 AS STARTFLAG,t.TABLE_SCHEMA, t.TABLE_NAME,'INSERT' AS FLAG
FROM #COLHELP T
UNION
SELECT CASE T.STARTFLAG WHEN 1 THEN 'UPDATE T1 SET ' ELSE '' END AS PREFIX,
'T1.'+T.NAME+'=T2.'+T.NAME AS NAME ,CASE T.ENDFLAG WHEN 1 THEN
' FROM '+t.TABLE_SCHEMA+'.'+t.TABLE_NAME+' T1 INNER JOIN T2' ELSE ',' END AS ENDFIX,
T.STARTFLAG,t.TABLE_SCHEMA, t.TABLE_NAME,'UPDATE' AS FLAG
FROM #COLHELP T
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#COLHELP'))
DROP TABLE #COLHELP
--SELECT * FROM #RESULT ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME,T.FLAG,t.startflag
IF @tblname='ALL'
SELECT t.PREFIX, t.NAME, t.ENDFIX,CASE t.STARTFLAG when 1 then '/*'+t.flag+' '+ t.TABLE_SCHEMA+'.'+ t.TABLE_NAME+'*/' ELSE '' END AS anno
FROM @result AS t ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME,t.flag,t.startflag
ELSE
SELECT t.PREFIX, t.NAME, t.ENDFIX,CASE t.STARTFLAG when 1 then '/*'+t.flag+' '+ t.TABLE_SCHEMA+'.'+ t.TABLE_NAME+'*/' ELSE '' END AS anno
FROM @result AS t
WHERE OBJECT_ID( t.TABLE_SCHEMA + '.' + t.TABLE_NAME)=OBJECT_ID(@tblname)
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME,t.flag,t.startflag
END
go
IF @@ERROR = 0 PRINT 'Procedure USP_GENERATEDML created'
go
EXEC [USP_GENERATEDML] 'PERSON.PERSON'
EXEC [USP_GENERATEDML] 'ALL'
TSQL 根据表名生成UPDATE SELECT INSERT
最新推荐文章于 2022-03-22 10:57:23 发布