TSQL 根据表名生成UPDATE SELECT INSERT

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

 

TSQL 根据表名生成UPDATE SELECT INSERT

上一篇:MySQL中varchar类型在5.0.3后的变化


下一篇:查看oracle数据库是否为归档模式