SQL sp to select from sysfiles

Hi,
I'm writing an sp to shrink the LDF file based on a database name passed to it however I know I can't use the 'use' command to reference the TEST database.

Is there anyway to use the literal database path based on a variable i.e.

select @fileid = fileid from TEST.dbo.sysfiles - works and I need
select @fileid = fileid from @DATABASE + TEST.dbo.sysfiles any idea's as I suspect this is something simple
LVL 5
NetstoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
You will probably need to write some dynamic SQL to do this for you.
0
dportasCommented:
Try: SELECT * FROM sys.master_files;

>> I'm writing an sp to shrink the LDF file based on a database name passed to it

Why? Shrink should be a very rare activity. Don't schedule or otherwise automate shrinking. Doing so is a major drain on resources and is entirely counter-productive in any well-managed environment.
0
NetstoreAuthor Commented:
You will probably need to write some dynamic SQL to do this for you.

Could you give me any more than this?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

chapmandewCommented:
Sure, here it is:
select top 0 * into ##temp from sys.database_files
 
 
declare @db nvarchar(200), @name sysname
 
DECLARE  CursorTemplate CURSOR 
FAST_FORWARD FOR 	
	SELECT name FROM sys.databases
 
OPEN CursorTemplate
 
FETCH NEXT FROM CursorTemplate 
INTO	@name 
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
	set @db = 'insert into ##temp select * from ' + @name + '.sys.database_files'
	exec sp_executesql @db
	
 
	FETCH NEXT FROM CursorTemplate 
	INTO	@name
 
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate
 
select * from ##temp

Open in new window

0
NetstoreAuthor Commented:
I have it working using dynamic SQL slightly different to yours but along the same principal.

So one final thing then I'll leave you alone 8) when I run my query it returns the results to the stdout window.

How do I pass my resuly back to a variable i.e. the file id as "set @file_id = sp_executesql @query" doesn't work
0
chapmandewCommented:
You're going to have several different values because there will be several databases.  You could throw the results from ##temp into another loop.  Does it have to be returned as a variable?  Can you not just do this:  
SELECT physical_name from ##temp

0
NetstoreAuthor Commented:
No I pass the database name in so there will only ever be one result
0
chapmandewCommented:
In that case just add this:
declare @path varchar(250)

select @path = physical_name from ##temp
where name = @dbname and type_sesc = 'rows'

It should return one result for the file (if there is only one).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NetstoreAuthor Commented:
cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.