Post reply

Error while storing results of sp_spaceused in a new table.

  • July 7, 2011 at 11:07 am

    #145589

    Hi All,

    I am getting the following error when run the below script.

    "Insert Error: Column name or number of supplied values does not match table definition."

    DECLARE name_cur CURSOR FOR SELECT DISTINCT db_name, obj_name from table1

    OPEN name_cur

    CREATE TABLE [dbo].[Table_MetaData](

    [name] [nvarchar](50)

    [rows] [int]

    [reserved] [varchar](50)

    [data] [varchar](50)

    [index_sze] [varchar](50)

    [unused] [varchar](50)

    )

    FETCH name_cur INTO @db_name, @table_name

    WHILE @@Fetch_Status = 0

    BEGIN

    SET @sql = @db_name+'..sp_spaceused '+ '"'+@table_name+'"'

    insert into [dbo].[Table_MetaData]

    EXEC (@sql)

    FETCH name_cur INTO @db_name, @table_name

    END

    Thank you!

  • July 7, 2011 at 11:31 am

    #1349896

    what you pasted was missing a lot...the table1 definition, three variables and more.

    i think your insert needs to be inside the @sql variable.

    SET @sql = ' insert into [master].[dbo].[Table_MetaData]([name],[rows],[reserved],[data],[index_sze],[unused]) '+ @db_name+'..sp_spaceused '+ '"'+@table_name+'"'

    EXEC (@sql)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 7, 2011 at 12:33 pm

    #1349932

    I have declared variables, just missed in the post. Table1 has two columns, Database name & Object name.

    I tried putting insert statement in @sql variable. But that gives an error "Incorrect syntax near 'tempDB'"

    tempDB is the database name.

  • July 7, 2011 at 12:38 pm

    #1349937

    ahh i see it:

    you need EXEC in there: can just have the proc name.,

    ...unused]) EXECUTE '+ @db_name+'..sp_spaceused ...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 7, 2011 at 12:42 pm

    #1349942

    Here is an alternative to that as well.

    http://jasonbrimhall.info/2010/05/25/space-used/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 7, 2011 at 12:59 pm

    #1349953

    @lowell: Thank you!! It worked 🙂

    I am trying the same for sp_depends. Do you think following would work?

    SET @sql = 'insert into [dbo].[Table_Dependency]([nam],[type]) EXECUTE USE '+@db_name+' EXEC sp_depends '+@table_name

    EXEC (@sql)

  • July 7, 2011 at 1:05 pm

    #1349956

    anjaliv (7/7/2011)

    @Lowell: Thank you!! It worked 🙂

    I am trying the same for sp_depends. Do you think following would work?

    SET @sql = 'insert into [dbo].[Table_Dependency]([nam],[type]) EXECUTE USE '+@db_name+' EXEC sp_depends '+@table_name

    EXEC (@sql)

    Yes

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 7, 2011 at 1:30 pm

    #1349981

    It says " Incorrect syntax near the keyword 'USE'."

  • July 7, 2011 at 1:39 pm

    #1349984

    Change to this:

    SET @sql = 'USE '+@db_name+'go; insert into [youradmindbname].[dbo].[Table_Dependency]([nam],[type]) EXEC sp_depends '+@table_name

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 7, 2011 at 1:40 pm

    #1349987

    do a PRINT @sql before you EXEC(@sql); that gives you the statement so you can run it independantly;

    look at the costruction in the middle where you inserted EXECUTE:

    ...EXECUTE USE ... that's not valid.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 7, 2011 at 1:54 pm

    #1350007

    Still gives an error "Incorrect syntax near 'go'"

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply