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

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

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

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

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

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

  • 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

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

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

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

  • Lynn,

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

  • 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

  • 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

  • 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

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

    4Column_name |Type |Computed|Length |Prec |Scale |Nullable|TrimTrailingBlanks|FixedLenNullInSource|Collation

    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!

  • 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

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

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

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