SQL Server 2000

  • 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

  • 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..

  • 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 🙁

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

  • 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

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

    John

  • 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

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

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