[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to get from sysdatabases to sysfiles

Posted on 2006-05-02
10
Medium Priority
?
1,527 Views
Last Modified: 2008-01-09
I have table called databases im going from here to
sys files

select * from DATABASES
   inner join MASTER.DBO.SYSDATABASES.name = DATABASES.Databasename
then i want to go this table and get the growth (which i assume is held in MG)

SELECT * FROM SYSFILES      name DataStream7i_data
                                                       DataStream7i_log
but i have different names  and only want to get the Data File

Any suggestions
0
Comment
Question by:TRACEYMARY
10 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 16588676
Please clarify what u wanted to get from this query?
0
 
LVL 20

Assisted Solution

by:Sirees
Sirees earned 800 total points
ID: 16588801
Just a wild guess...can you try this

select * from DATABASES
  inner join MASTER.DBO.SYSDATABASES b on b.name = DATABASES.Databasename
inner join MASTER.DBO.SYSFILES c on c.filename = b.filename
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16589036
The growth rate that i can see on EM when right click against Database for
  Data and then for the Log

I have a table that i built
LOG FILE
--------
DBNAME LOGSIZE LOGUSED LOG_FREE START_DATE FREESPACE
DBASEA   6464.99    36.15      6428.74      DATE              99%

DATA FILE
---------
DBNAME DATASIZE    DATAUSED    DATA_FREE      START_DATE FREESPACE
DBASEA 28500.00       26391.00        2109.00             DATE               7.4%

I was trying to go from DBSTATS that i have the DBNAME in it
to the SYSDATABASE and SYSFILES to get the growth value.

SELECT * from DBSTATS
  inner join MASTER.DBO.SYSDATABASES on name = DBSTATS.dbname

From Here i want to inner join this table to get the growth amount
i can do this to view the column i need but cannot work out relationiship between sysdatabases and sysfiles

SELECT * FROM SYSFILES to get the growth
here i get name
DataStream7i_data
DataStream7i_log
DataStream7i_index

But i only want the Growth rate for the DataStream7i_data

So that i can report the growth for the Data and for the Log which are expected to grow once i used all the
free space.  I take it im right that Data and Log can grow and shrink indepedantly.

Then i can take the growth and have another column called EXPECTED_MG

Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 20

Expert Comment

by:Sirees
ID: 16589398
>> can do this to view the column i need but cannot work out relationiship between sysdatabases and sysfiles
<<

you can join them by filename.

0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16589488
Ours do not match that nicely.

On sysdatabases i have name
DataStream7i_test

On sysfiles i have filename
E:\MSSQL\Data\DataStream7i_data.mdf                                                                                                E:\MSSQL\Data\DataStream7i_index.ndf                                                                                                E:\MSSQL\Data\DataStream7i_log.ldf                                                                                                    

Only want    
E:\MSSQL\Data\DataStream7i_data.mdf          

Some we have like this
DeltekCP
   filename DeltekCP_Reporting_Data

Makes it harder.                                                                                                                
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16591994
hey sirees i getting there
This is for LOG
SELECT dbid,  name, GROWTH
FROM dbo.sysaltfiles
WHERE (fileid = 2)

DATA
SELECT dbid,  name, GROWTH
FROM dbo.sysaltfiles
WHERE (fileid = 1)

Now i got to figure out the actual database name can give me a different name for the actual log and mdf file (do they call this logical name)



0
 
LVL 28

Accepted Solution

by:
imran_fast earned 1200 total points
ID: 16593659
DO YOU NEED THIS
=============
USE DataStream7i_test

SELECT
A.NAME, B.*, C.SIZE, C.GROWTH, C.NAME, C.FILENAME, C.MAXSIZE FROM MASTER.DBO.SYSDATABASES  A
INNER JOIN SYSFILES B
ON B.FILEID = 1
INNER JOIN MASTER.dbo.sysaltfiles C
ON C.DBID = A.DBID AND C.FILEID = 1
WHERE A.NAME = DB_NAME()
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16595119
Thats it .....great i been searching all over place....Very new to getting information from system files.

Once i got my entire database usage statistics i post for other people to use.....

Thanks have a great day
0
 
LVL 4

Expert Comment

by:csachdeva
ID: 16595447
You can get the file name from the SYSdatabases table in master...

USE MASTER
SELECT filename FROM sysdatabases
WHERE name = 'pubs'

returns....

filename
---------------------------------
c:\sql\data\MSSQL\data\pubs.mdf

Regards,
Chetan Sachdeva
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16605161
On the growth how would i determine if it is a % or a fixed MG amount
I get 10 and 6400 coming out where i know that 10% and a 6400 mg value

Cheers
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question