August 5, 2013 at 7:55 am
Hi can someone help please
i just did
sp_spaceused '[db].[db].[demo]'
where demo is my table
but i would like to get the rows, reserverd , data , index_size and unused only for some column in my demo table
how can i write this please
August 5, 2013 at 8:47 am
asco5 (8/5/2013)
Hi can someone help pleasei just did
sp_spaceused '[db].[db].[demo]'
where demo is my table
but i would like to get the rows, reserverd , data , index_size and unused only for some column in my demo table
how can i write this please
Not really sure what you are after but sp_spaceused is not going to work for columns.
From BOL:
"Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database."
The whole concept of trying to do this for a column just doesn't make sense. You can come up with the storage space for a column but not sure if that is what you are after either.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2013 at 9:09 am
I created a stored procedure you can use.
Sample data:
use tempdb;
IF OBJECT_ID('tempdb.dbo.demo') IS NOT NULL DROP TABLE dbo.demo;
CREATE TABLE dbo.demo (col1 varchar(1000), col2 varchar(1000));
WITH tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
INSERT INTO dbo.demo
SELECTCAST(REPLICATE(NEWID(),20) AS varchar(1000)),
CAST(REPLICATE(NEWID(),10) AS varchar(1000))
FROM tally WHERE n<=1000;
The stored proc:
USE tempdb --I'm using by tempdb, pick another db and update the proc accordingly
GO
CREATE PROC dbo.sp_spaceused2
(@table varchar(100),-- the table you want to examine
@cols varchar(1000))-- the columns
--(acceptable values: single value, multiple values seperated by comma, or "*")
AS
BEGIN
DECLARE @sql varchar(4000)='SELECT '+@cols+' INTO dbo.table_column FROM '+@table;
IF OBJECT_ID('tempdb..table_column') IS NOT NULL DROP TABLE tempdb..table_column;
EXEC(@sql);
EXEC sp_spaceused 'tempdb.dbo.table_column';
END
Examples:
EXEC dbo.sp_spaceused2 'dbo.demo','col1' --col1 only
EXEC dbo.sp_spaceused2 'dbo.demo','col2' --col2 only
EXEC dbo.sp_spaceused2 'dbo.demo','col1,col2' --col1 and col2
EXEC dbo.sp_spaceused2 'dbo.demo','*' --all columns (same as sp_spaceused)
I through this together real quick; make sure to test.
-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy