TSQL 根据表名生成UPDATE SELECT INSERT-白红宇

TSQL 根据表名生成UPDATE SELECT INSERT

阅读量：6722 次

发布时间：2019-06-25

本文共 3174 字，大约阅读时间需要 10 分钟。

USE [AdventureWorks2012]goIF object_id('USP_GENERATEDML') IS NOT NULLBEGIN     PRINT 'Dropping procedure USP_GENERATEDML'    DROP PROCEDURE [USP_GENERATEDML]      IF @@ERROR = 0 PRINT 'Procedure USP_GENERATEDML dropped'ENDgoCREATE PROCEDURE [USP_GENERATEDML] @TBLNAME NVARCHAR(100)ASBEGIN    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 @resultSELECT 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  UNIONSELECT 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 FLAGFROM #COLHELP T  UNIONSELECT 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 FLAGFROM #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 annoFROM @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 annoFROM @result AS tWHERE OBJECT_ID( t.TABLE_SCHEMA + '.' + t.TABLE_NAME)=OBJECT_ID(@tblname) ORDER BY  t.TABLE_SCHEMA, t.TABLE_NAME,t.flag,t.startflag ENDgoIF @@ERROR = 0 PRINT 'Procedure USP_GENERATEDML created'goEXEC [USP_GENERATEDML] 'PERSON.PERSON'EXEC [USP_GENERATEDML] 'ALL'

转载于:https://www.cnblogs.com/fuckcn/p/3944556.html

你可能感兴趣的文章