Parent Table Name for sp_spaceused
-
Can anybody help with this?
I need to modify the script below so that the result set shows the entire parent table name. Right now the script result set returns all the rows with the same table name as Changes_log, but I need the whole name for each table like P8905.Changes_log. I am using this script to see which changes_log tables need trimmed out to keep the db running better.
CREATE TABLE #TableSizes
(
table_name SYSNAME,
row_count int,
reserved_size varchar(10),
data_size varchar(10),
index_size varchar(10),
unused_size varchar(10)
)
INSERT #TableSizes
EXEC sp_MSforeachtable 'sp_spaceused ''?''', @whereand = "and name like '%changes_log%'"
SELECT * FROM #TableSizes ORDER BY table_name
Thanks!
-
Does your example table name P8905.Changes_log specify that the table owner is user id P8905? If not, I certainly wouldn't use a period as part of a table name. If you have many Changes_log tables with different owners, then you could use the following:
SET NOCOUNT ON
CREATE TABLE #tableNames
(
tblName sysname
)
CREATE TABLE #TableSizes
(
tblName sysname NULL,
table_name SYSNAME,
row_count int,
reserved_size varchar(10),
data_size varchar(10),
index_size varchar(10),
unused_size varchar(10)
)
INSERT #tableNames (tblName)
SELECT CONVERT(varchar(50), u.name + '.' + t.name)
FROM sysobjects t
JOIN sysusers u
ON t.uid = u.uid
WHERE t.type = 'U'
AND t.name LIKE '%changes_log%'
DECLARE @tbl varchar(50)
SELECT @tbl = Min(tblName) FROM #tableNames
WHILE @tbl IS NOT NULL
BEGIN
INSERT #TableSizes (table_name, row_count, reserved_size, data_size, index_size, unused_size)
EXEC sp_spaceused @tbl
UPDATE #TableSizes SET tblName = @tbl WHERE tblName IS NULL
-- next table
SELECT @tbl = Min(tblName)
FROM #tableNames
WHERE tblname > @tbl
END
SELECT * FROM #TableSizes ORDER BY table_name
DROP TABLE #tableNames
DROP TABLE #TableSizes
SET NOCOUNT OFF
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply