"Schoo" <scott.schuman@nospam.ma-hc.com> wrote in message
news:OnHXUDD$FHA.1548@TK2MSFTNGP10.phx.gbl...
>I need to set up an automated way to condense log files in a database.  I
> successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I
> was thinking that if I can create a stored proc that runs through all the
> tables in a dabase, I could schedule that to run and it would always keep
> my
> log file sizes down.  So I wrote code, but of course the first parameter
> of
> the "dbcc shrinkdatabase" command requires a 'table object' and that is my
> problem.  How can I write the code to use the table names as table objects
> instead of varchar vairables?  Here is the code that I have so far:
>
> declare @name as varchar(50)
> create table #tableNames
> (name varchar(50))
> insert into #tableNames select TABLE_NAME from INFORMATION_SCHEMA.TABLES
> while select count(*) from #tableNames > 0
> BEGIN
> @name = select top 1 from #tableNames
> dbcc shrinkdatabase (@name, truncateonly)
> delete from #tableNames where name = @name
> END
> drop table #tableNames
>
> Scott Schuman
> Medical Associates
>
>