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

SQL Server 2000 Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 2:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:00 AM
Points: 25, Visits: 184
Hi SQL Masters,

Greetings!

Do you have any idea where to pull these fields from sql server 2000?
Server Name DB Name File Type file_size MB File Name Disk Drive Drive Free Space (MB) SQLVersion




Thank you and Regards,

Tsinelas
Post #1506585
Posted Monday, October 21, 2013 3:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
Tsinelas (10/21/2013)
Hi SQL Masters,

Greetings!

Do you have any idea where to pull these fields from sql server 2000?
Server Name DB Name File Type file_size MB File Name Disk Drive Drive Free Space (MB) SQLVersion




Thank you and Regards,

Tsinelas


Hi Slippers,

Can you give us more details please??...Salamat

~Sam Pinto Regards..
Post #1506591
Posted Monday, October 21, 2013 6:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:00 AM
Points: 25, Visits: 184
Master Sam Pinto,

Salamat sa reply :)

Gusto ko sana makita ung filegroup total size in MB and filegroup free space in MB ng Databases kasama na rin ung file location via T-SQL :)

Sa SQL 2005 to 2008 kac dba, makikita sila sa sys.databases, sys.sysfiles & sys.sysfilegroups. Sa SQL 2000 kac d ko alam kung saan ko huhugutin ung infos



Post #1506639
Posted Monday, October 21, 2013 6:37 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 5:37 AM
Points: 498, Visits: 1,845
Is that Tagalog ? Can you write it again in English for us?

I'm not quite sure if this is what you want, but...

- for server name: SELECT @@SERVERNAME
- for database name of current database: select DB_NAME()

Post #1506654
Posted Monday, October 21, 2013 7:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:00 AM
Points: 25, Visits: 184
Hi Master SSC Journeyman,

Just asking if you guys have an idea on how to pull records of database information on SQL 2000, as of now on sql 2005 and 2008 i can get those information on sys.databases, sys.sysfiles & sys.sysfilegroups.

Any idea sir?



Thank you and Best Regards,

Tsinelas
Post #1506675
Posted Monday, October 21, 2013 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,383, Visits: 9,951
dbo.sysdatabases, dbo.sysfiles, dbo.sysfilegroups.

John
Post #1506680
Posted Thursday, October 24, 2013 5:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:00 AM
Points: 25, Visits: 184
Hi John,


Thank you very much!! :) I made this one. :)




Create table #DInfo([DDrive] nChar(1) Null, [MB_Free] Float Null)
Insert #DInfo EXEC master..xp_fixeddrives

DECLARE @vDBName nVarchar(200)
DECLARE @vSTR nVarchar(4000)

DECLARE InvDBs CURSOR FOR
SELECT rtrim(ltrim(NAME))
FROM dbo.sysdatabases d

OPEN InvDBs
FETCH InvDBs into @vDBName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @vSTR = 'USE ' + QUOTENAME(@vDBName) + CHAR(10)
SET @vSTR = @vSTR + '
SELECT
convert(nvarchar(128), serverproperty(''ServerName'')) As ''Server Name'',
DB_NAME() As ''DB Name'',
Case when SUBSTRING(a.filename,LEN(a.filename) -2,3) = ''mdf'' then ''Data'' else ''Log'' end As ''File Type'',
convert(decimal(12,2),round(a.size/128.000,2)) as file_size,
a.filename As ''File Name'',
Substring(a.filename,1,1) As ''Disk Drive'',
c.MB_Free As ''Drive Free Space (MB)'',
@@Version As ''SQLVersion''
FROM dbo.sysfiles a LEFT OUTER JOIN dbo.sysfilegroups b
ON a.groupid = b.groupid
INNER JOIN #DInfo c on Substring(a.filename,1,1) = c.DDrive
'
EXEC (@vSTR)
FETCH InvDBs into @vDBName
END

CLOSE InvDBs
DEALLOCATE InvDBs

DROP TABLE #DInfo








Tsinelas
Post #1507987
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse