Help with sp_help ( HOw do you capture the output to a table )

Question

Help with sp_help ( HOw do you capture the output to a table )

mw_sql_developer

SSCoach

Points: 19445
More actions
May 16, 2012 at 2:57 pm

#261152

Is there anyway that you could capture the output of sp_help <table_name> to some table. You know what I mean ?

Viewing 15 posts - 1 through 15 (of 25 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mw112009 (5/16/2012)
Is there anyway that you could capture the output of sp_help <table_name> to some table. You know what I mean ?

There is a way, using OPENQUERY. I just need to find the thread that where this was shown recently.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Can it be

http://www.sqlservercentral.com/Forums/Topic1267687-1292-1.aspx#bm1267987

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

here:

SELECT IDENTITY(INT,1,1) AS RowNum, *

INTO #MyFiles

FROM OPENROWSET('SQLOLEDB','Server=yourserver;Trusted_Connection=Yes;Database=YourDatabase',

'Set FmtOnly OFF; EXEC sp_help YourTable')

Be sure that you are allowed to execute adhoc queries. I had to modify my local system for this to work.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Unfortunately, that only captures the first result set. Not sure how to capture the rest of them.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

You may want to look at the code behind the procedure and see if you can use it to capture the information you need to a set of tables.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

mw112009 (5/16/2012)
Is there anyway that you could capture the output of sp_help <table_name> to some table. You know what I mean ?

What are you trying to do?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

EL Jerry SSCertifiable Points: 7063 More actions · Answer 7

mw112009 (5/16/2012)
Is there anyway that you could capture the output of sp_help <table_name> to some table. You know what I mean ?

I find it strange that this output is sent to a table. I have worked it out by setting Results to Text, then copying/pasting to other applications. Is there a specific reason why it has to go to a table?

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

mw_sql_developer SSCoach Points: 19445 More actions · Answer 8

Lynn,

All right, I wouldn't mind looking at the code. how can I get the code ? Help.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

EXEC sys.sp_helptext

@objname = N'dbo.sp_help';

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

WolfgangE SSCertifiable Points: 6328 More actions · Answer 10

mw112009 (5/16/2012)
Is there anyway that you could capture the output of sp_help <table_name> to some table. You know what I mean ?

hi there,

i don't know what information you want to get. You know there are many system objects you can query to get all information about a table? sys.columns, sys.indexes, sys.foreign_keys etc. So the information IS already in tables.

Greetings, WE

Mike Hinds SSCarpal Tunnel Points: 4258 More actions · Answer 11

weberharter (5/18/2012)
mw112009 (5/16/2012)
Is there anyway that you could capture the output of sp_help <table_name> to some table. You know what I mean ?
hi there,
i don't know what information you want to get. You know there are many system objects you can query to get all information about a table? sys.columns, sys.indexes, sys.foreign_keys etc. So the information IS already in tables.
Greetings, WE

I'm interested, too. If we had a command such as "EXEC sp_help Customers", could we direct the output of that statement to a temporary (or permanent) table, similarly to using a "SELECT INTO NewTable" type of statement?

Agreed the info already exists in tables "somewhere", but knowing this syntax, the actual location of such info would not need to be already known, or researched for hours.

Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

Lowell SSC Guru Points: 323518 More actions · Answer 12

right along the lines of what opc.three was suggesting, you could look at the code behind the scenes of sp_help and do something with it.

on my shared hosted server i have out in the web, i ended up making a modified version of sp_help i named "sp_helpweb", that gets all teh data that sp_help does, but puts it into a single table that has an Id and a single varchar(max) column;

at least with that, i can get similar information froma database i only have access to via a web page i created; i cannot connect via SSMs, and have't bothered to build a page that handles multiple result sets cleanly yet.

you can grab a copy of it here if you want:

sp_helpweb.txt

1Name |Owner |Type Created_datetime

2Tally |dbo |user table |Mar 24 2011 11:27AM

3----------------------------------------------------------------------------------------------------

5N |int |no |4 |10 |0 |no |(n/a) |(n/a) |NULL

6----------------------------------------------------------------------------------------------------

7Identity |Seed|Increment|Not For Replication

8N |1 |1 |0

9----------------------------------------------------------------------------------------------------

10RowGuidCol

11No rowguidcol column defined.

12----------------------------------------------------------------------------------------------------

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!

WolfgangE SSCertifiable Points: 6328 More actions · Answer 13

Mike Hinds (5/18/2012)
...
...
I'm interested, too. If we had a command such as "EXEC sp_help Customers", could we direct the output of that statement to a temporary (or permanent) table, similarly to using a "SELECT INTO NewTable" type of statement?
Agreed the info already exists in tables "somewhere", but knowing this syntax, the actual location of such info would not need to be already known, or researched for hours.

Hello Mike,

my english is not that well and i'm not sure i understood your question(s).

The one thing is: yes, you can direct the output of a stored procedure to a table using this syntax:

insert into TableXY

execute ProcedureXY

TableXY has to exist. It's not possible to create the table AND save the output within only command like you can do it using select * into TableA from TableB

But, using this syntax, it's possible to only store 1 resultset. If a procedure returns multiple result you get only the first or the last result (i'm not sure for now) to the table.

There are also other procedures for all parts of information you get from sp_help. There is sp_helpindex or sp_helpconstraint, maybe these procedures are easier to catch.

Still I think it is the wrong way storing all these data in own tables as the information already exists in the system. Its better to find out how to get the data from the system. This is my personal opinion.

Have a look at book onlines and search for sys.columns, sys.indexes and so on or google for some skripts, i'm sure there will be lots of them to get all the data you want.

Or, as already mentioned, get the code of the procedure sp_help to find out where to get the data from.

Nice evening, WE

mw_sql_developer SSCoach Points: 19445 More actions · Answer 14

Folks

Thanks, I am done.

I managed to look inside the sp_help and get the part what I wanted.

I only wanted the column definitions to go to a table. Why I wanted to

write a generic stored proc that will take a table name and return a

bunch on insert statements with data

What I mean is if table Tab_A has 2 rows of data I want the output to be something like.

INSERT INTO Tab_A ( ID, Name ) VALUE ( 1, 'AbC' );

INSERT INTO Tab_A ( ID, Name ) VALUE ( 2, 'XYZ' );

That's my simple backup ( In case a user deletes my tables, i have a backup instead of having to go through backup, restore all that stuff.. )