Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Script Expand / Collapse
Author
Message
Posted Friday, November 9, 2007 4:10 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:11 AM
Points: 448, Visits: 406
If you remove the where clause in given script then it will display the information about transaction log file coz' the file id of .mdf file is 1 and fileid of .ldf file is 2. The below block of the code will give the required information even if you are having multiple data files I mean primary(.mdf) and secondary(.ndf) or multiple log files because there is no where clause used. Follow the below script.

Open c
fetch next from c into @db
while @@fetch_status = 0
begin
Exec ('INSERT #t
select ''' + @db + ''', filename, name, (size * 8)/1024, maxsize, growth
from ' + @db + '..sysfiles')
-- dont use where clause here coz' it will restrict the result

I have executed above script successfully.
try it...

Regards,
Austin
Post #420723
Posted Friday, November 9, 2007 4:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
Susan S (11/8/2007)
I mean the above script will produce.

Name of database
Logical file name of .MDF
.Mdf File location drive
Size of the file in MB
Physical file location(full path of .mdf file)
Maxsize
Growth

and I want to add

Logical file name of .LDF
.LDF File location drive
Size of the file in MB
Physical file location(full path of .ldf file)


:)


No, no... wasn't directed at you, Susan... Russell's thread had "edit math error" at the bottom and I wanted to know what he meant by that...


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420728
Posted Sunday, November 11, 2007 5:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 7:59 PM
Points: 284, Visits: 92
Thanks Austin.
It works well :)
Post #420865
Posted Sunday, November 11, 2007 10:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
Just a different slant on things...

--  DROP TABLE #T
GO
--===== 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 @SQL VARCHAR(8000)

--===== Create all the commands necessary for ALL databases
SELECT @SQL = ISNULL(@SQL+CHAR(13),'')
+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',Name)
FROM Master.dbo.SysDatabases

--===== Execute the commands
EXEC (@SQL)

--===== Display the results
SELECT * FROM #T ORDER BY Name



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420916
Posted Monday, November 12, 2007 3:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
Nice one Jeff. I am adding this one to my toolbelt. :D



My blog: http://jahaines.blogspot.com
Post #421239
Posted Monday, November 12, 2007 8:20 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:11 AM
Points: 448, Visits: 406
Nice script Jeff, thanks
Post #421301
Posted Monday, November 12, 2007 9:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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 :P ).

  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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #421319
Posted Monday, November 12, 2007 10:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 7:59 PM
Points: 284, Visits: 92
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
Post #421320
Posted Monday, November 12, 2007 10:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
Remove the DROP TABLE and the GO... then, it should work as a sproc.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #421322
Posted Monday, November 12, 2007 10:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #421323
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse