SQL Server 2000

Question

SQL Server 2000

Tsinelas

Right there with Babe

Points: 757
More actions
October 21, 2013 at 2:46 am

#281595

Hi SQL Masters,
Greetings!
Do you have any idea where to pull these fields from sql server 2000?
Server NameDB NameFile Typefile_size MBFile NameDisk DriveDrive Free Space (MB)SQLVersion
Thank you and Regards,
Tsinelas

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 1

Tsinelas (10/21/2013)
Hi SQL Masters,
Greetings!
Do you have any idea where to pull these fields from sql server 2000?
Server NameDB NameFile Typefile_size MBFile NameDisk DriveDrive Free Space (MB)SQLVersion
Thank you and Regards,
Tsinelas

Hi Slippers,

Can you give us more details please??...Salamat:-)

~Sam Pinto Regards..

Tsinelas Right there with Babe Points: 757 More actions · Answer 2

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 🙁

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

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()

Tsinelas Right there with Babe Points: 757 More actions · Answer 4

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

John Mitchell-245523

SSC Guru

Points: 148809

October 21, 2013 at 7:29 am

#1660136

dbo.sysdatabases, dbo.sysfiles, dbo.sysfilegroups.

John

Tsinelas Right there with Babe Points: 757 More actions · Answer 6

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