Error while storing results of sp_spaceused in a new table.

Question

Post reply

Error while storing results of sp_spaceused in a new table.

anjaliv

Say Hey Kid

Points: 689
More actions
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!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

anjaliv Say Hey Kid Points: 689 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

anjaliv Say Hey Kid Points: 689 More actions · Answer 5

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

anjaliv Say Hey Kid Points: 689 More actions · Answer 7

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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

Lowell SSC Guru Points: 323518 More actions · Answer 9

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!

anjaliv Say Hey Kid Points: 689 More actions · Answer 10

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