October 26, 2004 at 11:18 am
Trying to put together a script based on some other consturcts found on this site and having syntax issues.
I'm trying to get for each database, the related table space information so I can save it to a capacity planning database and keep tabs on it periodically.
I want to look thru each database, obtain table space info and insert into a table.
This is an excerpt out of the TSQL where having the issues:
declare @dbname varchar(255)
,@sqlcmd nvarchar(255)
declare db cursor for select name
from master..sysdatabases
where lower(name) not in('master','model','tempdb')
open db
fetch next from db into @dbname
WHILE @@FETCH_STATUS <> -1
begin
select @sqlcmd = 'use ' + @dbname
print @dbname
-- exec(@sqlcmd)
exec sp_executesql @sqlcmd
insert dba..tmp_table_size exec sp_MsForeachTable 'exec sp_spaceused ''?''--,true'
-- "uncomment" --,true above to run updateusage
October 26, 2004 at 1:09 pm
See if this helps:
DECLARE @SqlStatement nvarchar(4000)
DECLARE @DatabaseName sysname
DECLARE DatabaseNames CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
OPEN DatabaseNames
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseNames
INTO @DatabaseName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement = N'USE ' +
QUOTENAME(@DatabaseName) +
N' DECLARE @SqlStatement nvarchar(4000)
DECLARE SqlStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N''USE '' +
QUOTENAME(DB_NAME()) +
N'' EXEC sp_spaceused '''''' +
QUOTENAME(TABLE_SCHEMA) +
N''.'' +
QUOTENAME(TABLE_NAME) +
N''''''''
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = ''BASE TABLE'' AND
OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
''.'' +
QUOTENAME(TABLE_NAME)),
''IsMSShipped'') = 0
OPEN SqlStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM SqlStatements
INTO @SqlStatement
IF @@FETCH_STATUS = -1 BREAK
PRINT(@SqlStatement)
EXEC(@SqlStatement)
END
CLOSE SqlStatements
DEALLOCATE SqlStatements'
EXEC(@SqlStatement)
END
CLOSE DatabaseNames
DEALLOCATE DatabaseNames
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply