how to reference a table in a variable?
-
im trying
to truncate all the tables in a given database.
im writing a sproc
to do this ( for testing purposes)
here it
is so far:
-----------------------------------------
Declare TableCursor cursor
For
select
name from sysobjects
where
type = 'U'
open
tablecursor
fetch
next from tablecursor into @table
begin
truncate
@table
fetch
next from tablecursor ito table
end
deallocate tablecursor
-----------------------------------------
my problem is , what datatype should @table be?
i want it to reference the table to be truncated
-
Open BOL and find the difference between variables and identifiers.
_____________
Code for TallyGenerator -
Hi James,
Use sysname as the datatype.
The truncate table statement must be dynamic and can be written as follows:
exec
('truncate table ' + @tablename)
Or, better still, you could use the sp_executesql procedure.
e.g.
- declare
@cmd nvarchar(4000)
- set
@cmd = 'truncate table ' + @tablename
- exec
sp_executesql @cmd
Or, even better still, use the sp_msforeachtable system procedure and you can do the whole thing in one line of code without the need to write out the cursor.
exec
sp_msforeachtable 'truncate table ?'
Remember though that the truncate table statement will fail if the table you're trying to truncate is referenced by foreign key constraints.
And it goes without saying that you should backup your database before running the code
-
worked a charm. thanks a million
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply