本文介绍了将查询转换为storedprocedure的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何将此查询更改为存储过程?我需要一个相同的结果集



how to change this query in to stored procedure ? I need a same result set

DECLARE @DBName AS VARCHAR(100)
DECLARE @TotalUserTables INT
DECLARE @TotalStoredProcedures INT
DECLARE @TotalViews INT

 CREATE TABLE #tblDBResults1
        (
         DatabaseName VARCHAR(100)
        ,Item VARCHAR(50)
        ,Total_count INT
        )
 DECLARE @SQL VARCHAR(1000)
    /* basic select statement */
DECLARE @SQLWithDB VARCHAR(1000)
    /* select statement with USE db added */

SET @TotalUserTables = 0
SET @TotalStoredProcedures = 0
SET @TotalViews = 0


  SET @SQL = 'SELECT DB_NAME(),
                    [type]
                   ,total_count = COUNT(*)
            FROM    (SELECT type = CASE WHEN type = ''U'' THEN ''User tables''
                                        WHEN type = ''V'' THEN ''Views''
                                        WHEN type = ''P'' THEN ''Stored procs''
										WHEN type = ''FN'' THEN ''Functions''
                                   END
                     FROM   sys.objects
                     WHERE  type IN (''P'', ''U'', ''V'',''FN'')
                    ) s
            GROUP BY type;'

         DECLARE DatabaseResultsetCursor CURSOR
         FOR
                 SELECT name
                 FROM   sys.databases
                 WHERE  Database_id > 4
                        AND sys.databases.state = 0
                /* exclude system and offline databases */
				OPEN DatabaseResultsetCursor
FETCH NEXT FROM DatabaseResultsetCursor INTO @DBName

WHILE @@fetch_status = 0
      BEGIN

            SET @SQLWithDB = 'USE [' + @DBName + '];' + CHAR(10) + CHAR(13)
                + @SQL

            INSERT  #tblDBResults1
                    EXEC (@SQLWithDB)

            FETCH NEXT FROM DatabaseResultsetCursor INTO @DBName

      END

CLOSE DatabaseResultsetCursor
DEALLOCATE DatabaseResultsetCursor

SELECT DatabaseName,
      MAX(CASE WHEN Item  = 'User tables' THEN Total_count ELSE '0'  END) UserTables,
      MAX(CASE WHEN Item = 'Stored procs' THEN Total_count ELSE '0' END) StoredProcedure,
	   MAX(CASE WHEN Item  = 'Functions' THEN Total_count ELSE '0'  END) Functions,
      MAX(CASE WHEN Item  = 'Views' THEN Total_count ELSE '0'  END) Views

FROM #tblDBResults1
GROUP BY DatabaseName
ORDER BY DatabaseName
DROP TABLE #tblDBResults1  





我尝试过:



实际上我手边不知道我是SQL的初学者



What I have tried:

Actually I have no idea in my hand I am a beginner of SQL

推荐答案



这篇关于将查询转换为storedprocedure的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 12:18
查看更多