InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:38:44 GMT20http://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxYou bet... thank all of you folks for the feedback.Tue, 13 Nov 2007 00:11:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxThanks JeffMon, 12 Nov 2007 22:51:23 GMTSusan Shttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxI 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 :D[code]--===== 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 variablesDECLARE @SQL NVARCHAR(4000) --For dynamic SQLDECLARE @DBName SYSNAME --Name of current database being worked--===== Get the names for all databasesDECLARE 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 CursorDataBasesDEALLOCATE CursorDataBases--===== Display the results from the scratchpad table SELECT * FROM #T[/code]Mon, 12 Nov 2007 22:37:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxAnd, unless you're going to put the sproc in the Master database, don't use sp_ as the beginning of the sproc name.Mon, 12 Nov 2007 22:27:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxRemove the DROP TABLE and the GO... then, it should work as a sproc.Mon, 12 Nov 2007 22:26:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxThanks 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 [b]CREATE PROCEDURE SP_CheckDBAS[/b]DROP TABLE #TGO--===== 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 2k5DECLARE @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?ThanksMon, 12 Nov 2007 22:04:45 GMTSusan Shttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxAck... 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 ).[code] DROP TABLE #TGO--===== 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 2k5DECLARE @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[/code]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...Mon, 12 Nov 2007 21:44:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxNice script Jeff, thanksMon, 12 Nov 2007 20:20:23 GMTAustin_123http://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxNice one Jeff. I am adding this one to my toolbelt. :DMon, 12 Nov 2007 15:08:57 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxJust a different slant on things...[code]-- DROP TABLE #TGO--===== 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 2k5DECLARE @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[/code]Sun, 11 Nov 2007 22:40:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxThanks Austin.It works well :)Sun, 11 Nov 2007 17:16:01 GMTSusan Shttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspx[quote][b]Susan S (11/8/2007)[/b][hr]I mean the above script will produce.Name of databaseLogical file name of .MDF.Mdf File location driveSize of the file in MBPhysical file location(full path of .mdf file)Maxsize Growthand I want to add [i]Logical file name of .LDF.LDF File location driveSize of the file in MBPhysical file location(full path of .ldf file)[/i]:)[/quote]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...Fri, 09 Nov 2007 16:48:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxIf 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 cfetch next from c into @dbwhile @@fetch_status = 0beginExec ('INSERT #tselect ''' + @db + ''', filename, name, (size * 8)/1024, maxsize, growthfrom ' + @db + '..sysfiles') -- dont use where clause here coz' it will restrict the resultI have executed above script successfully.try it...Regards,AustinFri, 09 Nov 2007 16:10:45 GMTAustin_123http://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxI mean the above script will produce.Name of databaseLogical file name of .MDF.Mdf File location driveSize of the file in MBPhysical file location(full path of .mdf file)Maxsize Growthand I want to add [i]Logical file name of .LDF.LDF File location driveSize of the file in MBPhysical file location(full path of .ldf file)[/i]:)Thu, 08 Nov 2007 22:24:10 GMTSusan Shttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspx[quote]edit math error... [/quote]Ummm... what's that mean? :blink:Thu, 08 Nov 2007 22:18:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxbtw -can we capture the log file too?where do we add it on the script?Thanks,SusanThu, 08 Nov 2007 18:38:51 GMTSusan Shttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxThis script is very useful.Thanks for that I can use this too for my server.Thu, 08 Nov 2007 18:31:09 GMTSusan Shttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxThanks for the script..I used the script successfully which u have posted with little bit of editing coz' I am using SQL 2005 environment. Thanks a lot...Regards,AustinThu, 08 Nov 2007 15:49:28 GMTAustin_123http://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxu will need to modify if multiple data files. also note that maxsize and growth are in 8kb increments, so need to multiply by 128 to get mb. but, the values may be -1 for maxsize meaning unlimited and/or 0 for growth meaning no growth. u can add logic for those values if need.oh yeah, run script in master dbThu, 08 Nov 2007 12:15:08 GMTrussell-154600http://www.sqlservercentral.com/Forums/Topic420195-146-1.aspx-- this will get u started --SET NOCOUNT ONCreate Table #t ( db varchar(255), filename varchar(255), name varchar(255), [size in MB] int, maxsize int, growth int)Declare @db varchar(255)Declare c Cursorread_onlyfor select name from sysdatabases where dbid > 4Open cfetch next from c into @dbwhile @@fetch_status = 0begin Exec ('INSERT #t select ''' + @db + ''', filename, name, (size * 8)/1024, maxsize, growth from ' + @db + '..sysfiles where fileid = 1' ) fetch next from c into @dbendClose cDeallocate cSelect * from #tDrop table #t[b]edit[/b] math error... :blink:Thu, 08 Nov 2007 12:12:35 GMTrussell-154600http://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxNo I don't have script that is the reason I am looking for somebuddy who can help me.Thu, 08 Nov 2007 11:17:01 GMTAustin_123http://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxDo you have a script to do this one at a time?Thu, 08 Nov 2007 11:10:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic420195-146-1.aspxHi All,I have around 80 databases in my one of sql server instance.we hve to regularly send the database wise health report to the client.The following fields mentioned by the client in excel sheetName of databaseLogical file name of .MDF.Mdf File location driveSize of the file in MBPhysical file location(full path of .mdf file)Maxsize GrowthNow a days we have to check databases one by one and its taking very much time.Could anyone provide me the script which automatically fetch all of required fields information from all of the databases.Urgent help will be appreciable.AustinThu, 08 Nov 2007 10:43:39 GMTAustin_123