April 29, 2010 at 4:21 am
Hi,
I am trying something along the lines of the following:
declare@SERVER_DB1 VARCHAR (100)
set @SERVER_DB1 ='mydatabase'
CREATE TABLE #DB1
(table_name varchar(100),
rows int,
reserved varchar (50),
data varchar(50),
index_size varchar(50),
unused varchar(50))
INSERT INTO #DB1 EXECUTE @SERVER_DB1.dbo.sp_msforeachtable 'sp_spaceused "?"'
I get a syntax error in the insert statement - it does not like :@SERVER_DB1.dbo.sp_msforeachtable
The error I get is: Incorrect syntax near '.'.
Can one use a variable like this?
Thanks!
Jinx.
April 29, 2010 at 5:04 am
Jinx-640161 (4/29/2010)
I get a syntax error in the insert statement - it does not like :@SERVER_DB1.dbo.sp_msforeachtableThe error I get is: Incorrect syntax near '.'.
Can one use a variable like this?
No you can not.. U could still utlize dynamic sql to run the above
like
DECLARE @QUERY VARCHAR(4000)
SET @QUERY = 'INSERT INTO #DB1 EXECUTE '+@SERVER_DB1+'.dbo.sp_msforeachtable ''sp_spaceused "?"'''
PRINT @QUERY
exec (@QUERY)
April 29, 2010 at 5:17 am
DECLARE @DatabaseName SYSNAME;
SET @DatabaseName = QUOTENAME(N'AdventureWorks');
DECLARE @data
TABLE (
table_name SYSNAME PRIMARY KEY,
row_count INTEGER NOT NULL,
reserved VARCHAR(50) NOT NULL,
data VARCHAR(50) NOT NULL,
index_size VARCHAR(50) NOT NULL,
unused VARCHAR(50) NOT NULL
);
INSERT @data
EXECUTE (
@DatabaseName + N'.sys.sp_msforeachtable ''sp_spaceused "?"'''
);
SELECT *
FROM @data;
edit: ColdCoffee beat me to it
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 29, 2010 at 6:08 am
Perfect!
Thanks to both of you. 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply