sp_spaceused columns?

  • 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

  • asco5 (8/5/2013)


    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

    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/

  • 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- 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