November 12, 2007 at 8:20 pm
Nice script Jeff, thanks
November 12, 2007 at 9:44 pm
Ack... I forgot that the Name column in SysDatabases is of the SysName datatype which is actually NVARCHAR... cuts the capabilities of the script in half without a conversion.
Also, 8000 characters isn't much to work with in SQL Server 2000... we have a server instance that has 445 databases with pretty big names on it (not MY idea 😉 ). So, I rewrote the script a bit... it's still not unlimited like a cursor or while loop would be, though... Varchar(MAX) would help a lot in SQL Server 2k5 but even that wouldn't allow for unlimited (although you'd be in a lot worse shape than I if you needed it that big 😛 ).
DROP TABLE #T
GO
--===== Supress the auto-display of rowcounts
SET NOCOUNT ON
--===== Create a table to store the results in
CREATE TABLE #T
(
Name SYSNAME,
FileID INT,
FileName NVARCHAR(512),
FileGroup VARCHAR(100),
Size VARCHAR(20),
MaxSize VARCHAR(20),
Growth VARCHAR(20),
Usage VARCHAR(20)
)
--===== Declare a local variable for some dynamic SQL... Could use VARCHAR(MAX) in 2k5
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
DECLARE @SQL4 VARCHAR(8000)
--===== Create all the commands necessary for ALL databases
SELECT @SQL4 = CASE WHEN LEN(ISNULL(@SQL4,' ')) =7700
THEN ISNULL(@SQL4,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL4
END,
@SQL3 = CASE WHEN LEN(ISNULL(@SQL3,' ')) =7700
THEN ISNULL(@SQL3,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL3
END,
@SQL2 = CASE WHEN LEN(ISNULL(@SQL2,' ')) =7700
THEN ISNULL(@SQL2,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL2
END,
@SQL1 = CASE WHEN LEN(ISNULL(@SQL1,' '))<7700
THEN ISNULL(@SQL1,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL1
END
FROM Master.dbo.SysDatabases
--===== Execute all the SQL...
EXEC (@SQL1+@SQL2+@SQL3+@SQL4)
--===== Display the results
SELECT * FROM #T ORDER BY Name
I hate to admit this, but a WHILE loop may be better for scalability in this case (although the code above handled 445 long database names)... especially if you have to mod the code with NVARCHAR to contend with non-US database names.
The side benefit of the code is that it does show a method for making some pretty long dynamic SQL on SQL Server 2k...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2007 at 10:04 pm
Thanks for the script Jeff.
can I create this script as store proc and run it in reporting service?
I try to do it but it doesn't work
when I put
CREATE PROCEDURE SP_CheckDB
AS
DROP TABLE #T
GO
--===== Supress the auto-display of rowcounts
SET NOCOUNT ON
--===== Create a table to store the results in
CREATE TABLE #T
(
Name SYSNAME,
FileID INT,
FileName NVARCHAR(512),
FileGroup VARCHAR(100),
Size VARCHAR(20),
MaxSize VARCHAR(20),
Growth VARCHAR(20),
Usage VARCHAR(20)
)
--===== Declare a local variable for some dynamic SQL... Could use VARCHAR(MAX) in 2k5
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
DECLARE @SQL4 VARCHAR(8000)
--===== Create all the commands necessary for ALL databases
SELECT @SQL4 = CASE WHEN LEN(ISNULL(@SQL4,' ')) =7700
THEN ISNULL(@SQL4,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL4
END,
@SQL3 = CASE WHEN LEN(ISNULL(@SQL3,' ')) =7700
THEN ISNULL(@SQL3,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL3
END,
@SQL2 = CASE WHEN LEN(ISNULL(@SQL2,' ')) =7700
THEN ISNULL(@SQL2,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL2
END,
@SQL1 = CASE WHEN LEN(ISNULL(@SQL1,' '))<7700
THEN ISNULL(@SQL1,'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))
+CHAR(13)
ELSE @SQL1
END
FROM Master.dbo.SysDatabases
--===== Execute all the SQL...
EXEC (@SQL1+@SQL2+@SQL3+@SQL4)
--===== Display the results
SELECT * FROM #T ORDER BY Name
any comment on this?
Thanks
November 12, 2007 at 10:26 pm
Remove the DROP TABLE and the GO... then, it should work as a sproc.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2007 at 10:27 pm
And, unless you're going to put the sproc in the Master database, don't use sp_ as the beginning of the sproc name.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2007 at 10:37 pm
I just got done doing a conversion for "unlimited scalability" using a (yeeeeaaacchh!) Cursor (haaaaaaaaaack! Patoooooiiiii!)... it takes 17 times longer to run even though I wrote it as a "fire-hose" cursor... still, I did make it so it returns everything as a single results set. If anyone wants it, here it is... I gotta go brush my teeth to get the bad taste out of my mouth 😀
--===== If the scratchpad table already exists, drop it
IF OBJECT_ID('TempDB..#T','U') IS NOT NULL
DROP TABLE #T
--===== Supress the auto-display of rowcounts
SET NOCOUNT ON
--===== Create a table to store the results in
CREATE TABLE #T
(
Name SYSNAME,
FileID INT,
FileName NVARCHAR(512),
FileGroup VARCHAR(100),
Size VARCHAR(20),
MaxSize VARCHAR(20),
Growth VARCHAR(20),
Usage VARCHAR(20)
)
--===== Declare a local variables
DECLARE @sql NVARCHAR(4000) --For dynamic SQL
DECLARE @DBName SYSNAME --Name of current database being worked
--===== Get the names for all databases
DECLARE CursorDataBases CURSOR FORWARD_ONLY --Read only fire-hose cursor
FOR
SELECT Name
FROM Master.dbo.SysDatabases
OPEN CursorDataBases
FETCH NEXT FROM CursorDataBases
INTO @DBName
--===== Execute sp_helpfile for each database name (we already got the first one)
-- and save the results in the scratchpad table
WHILE @@FETCH_STATUS = 0
BEGIN print @dbname
SET @sql = REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',@DBName)
EXEC (@SQL)
FETCH NEXT FROM CursorDataBases
INTO @DBName
END
--======== Do some housekeeping
CLOSE CursorDataBases
DEALLOCATE CursorDataBases
--===== Display the results from the scratchpad table
SELECT * FROM #T
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2007 at 10:51 pm
Thanks Jeff
November 13, 2007 at 12:11 am
You bet... thank all of you folks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply