October 19, 2010 at 4:13 am
Hi,
i Have one sp which givings results 20 rows But COLUMN HEADINGS ARE REPEATING FOR each row.
How to eliminate repeting headers or loop to get column headers only one time without using temp,table Creation.
Because i would like to execute sp in open row set in another instance and sp is as fallows
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_abcd]
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
DECLARE @vDB_Name AS NVARCHAR (500)
DECLARE @vSQL_String AS NVARCHAR (MAX)
SET @vDB_Name = (SELECT TOP 1 DB.name FROM [master].[sys].[databases] DB WHERE DB.state = 0 AND DB.is_read_only = 0 AND DB.is_in_standby = 0 AND DB.source_database_id IS NULL ORDER BY DB.name)
WHILE @vDB_Name IS NOT NULL
BEGIN
SET @vSQL_String =
'
USE ['+@vDB_Name+'];
SELECT
@@servername as Server_name
,DB_NAME () AS database_name
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND ((A.total_size*CONVERT (BIGINT, 8192))/1048576.0, 0)), 1)), 4, 15)) AS total_size_mb
,(CASE
WHEN A.database_size >= B.total_pages THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND (((A.database_size-B.total_pages)*CONVERT (BIGINT, 8192))/1048576.0, 0)), 1)), 4, 15))
ELSE ''0''
END) AS unallocated_mb
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND ((B.total_pages*CONVERT (BIGINT, 8192))/1048576.0, 0)), 1)), 4, 15)) AS reserved_mb
FROM
(
SELECT
SUM (CASE
WHEN DBF.type = 0 THEN DBF.size
ELSE 0
END) AS database_size
,SUM (DBF.size) AS total_size
FROM
[sys].[database_files] AS DBF
WHERE
DBF.type IN (0,1)
) A
CROSS JOIN
(
SELECT
SUM (AU.total_pages) AS total_pages
,SUM (AU.used_pages) AS used_pages
,SUM (CASE
WHEN IT.internal_type IN (202,204) THEN 0
WHEN AU.type <> 1 THEN AU.used_pages
WHEN P.index_id <= 1 THEN AU.data_pages
ELSE 0
END) AS pages
FROM
[sys].[partitions] P
INNER JOIN [sys].[allocation_units] AU ON AU.container_id = P.partition_id
LEFT JOIN [sys].[internal_tables] IT ON IT.[object_id] = P.[object_id]
) B
'
EXEC (@vSQL_String)
SET @vDB_Name = (SELECT TOP 1 DB.name FROM [master].[sys].[databases] DB WHERE DB.state = 0 AND DB.is_read_only = 0 AND DB.is_in_standby = 0 AND DB.source_database_id IS NULL AND DB.name >@vDB_Name ORDER BY DB.name)
END
--
October 19, 2010 at 6:24 am
Hi,
Why not just append the data to global temp table?:
create table ##myResults (result1 varchar... etc...)
while...
set @sql = 'insert ##myResults select Value1... etc
exec @sql
<next db>
end
select * from ##myResults
or, you could return the values (assuming you only get one row of data) to your proc using sp_executesql instead of exec(@sql) and insert to a table variable local to the proc.
Regards, Iain
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply