hi
i want to retrieve all columns of all tables by this code :
exec sp_msforeachtable 'exec sp_columns ''?'''
but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.
Hi y'all,
I found following sql statement:
How is it possible to make this general for a certain database? So i can get this information with a project parameter so i don't need to store this stored procedure in each single database?
CREATE TABLE #temp ( table_name sysname , row_count int, reserved_size varchar(50), data_size varchar(50), index_size varchar(50), unused_size varchar(50))SET NOCOUNT ONINSERT #tempEXEC sp_msforeachtable 'sp_spaceused ''?'''SELECT a.table_name, a.row_count, count(*) as col_count, a.data_sizeFROM #temp aINNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_defaultGROUP BY a.table_name, a.row_count, a.data_sizeORDER BY CAST(Replace(a.data_size, ' KB', '') as integer) descDROP TABLE #temp
Thanks!
What does the following SQL code mean?
EXEC sp_msforeachtable 'sp_spaceused ''?'''
Hi,
If anybody know the source (links) with information about SP like this one.
sp_MSdependencies and so on.
thanks.
I'm having trouble getting the syntax of this snippet of code to work. The big issue is with
the SP 'sp_MSforeachtable' using the cmd 'BDCC DBREINDEX'...
-- Declare the variables.
DECLARE @database varchar(75)
Declare @str varchar(1000)
Set @database = 'pubs'
SELECT @str = 'EXEC ' + @database + '.dbo.sp_MSforeachtable @command1=''DBCC DBREINDEX (''''?'''')''' + Char(13)
print @str
exec @str
/* results are:
EXEC pubs.dbo.sp_MSforeachtable @command1='DBCC DBREINDEX (''?'')'
Server: Msg 911, Level 16, State 1, Line 7
Could not locate entry in sysdatabases for database 'EXEC pubs'. No entry found with that name. Make sure that the name is entered correctly.
*/
Hi,
what I basically want to do is to delete all records from all tables where the field loadnumber equals a certain value.
To do this I've defined the global variable 'DSLoadNumber' (integer) in a DTS package, and I want to use the stored procedure sp_msforeachtable in an Execute SQL Task.
This is the code in the Execute SQL Task:
sp_msforeachtable
@replacechar = '~',
@command1 = "delete from ~ where loadnumber = ?",
@whereand = " and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')"
This code can be parsed without any problem, but the ? is not seen as a variable.
When I get the ? out of the quotes the code cannot be parsed any longer.
Finally I've written a cursor to the job which looks like this:
DECLARE @tablename varchar(35),
@loadnumber int
SELECT @loadnumber = ?
DECLARE delete_cursor CURSOR FOR
select name
from sysobjects
where type = 'U'
and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')
OPEN delete_cursor
FETCH delete_cursor INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
exec('delete from ' + @tablename + ' where loadnumber = ' + @loadnumber)
FETCH NEXT FROM delete_cursor INTO @tablename
END
CLOSE delete_cursor
DEALLOCATE delete_cursor
But this code doesn't compile either...
Any suggestions on how to get the code working within DTS?
Stef