sp_getrecords to get all records from a table

  • Hi,

    I have tried and created one sp which will get the table name as input and will return all the records in that table. I will map this sp in the SSMS keyboard shortcut (just like Alt + F1 to return the table structure) so that i can use this shortcut to get the records from that table instead of writing some thing like SELECT * FROM tablename. this sp works fine and helped me a lot. however, if the table is belong to a schema then we have to pass the table name along with schema like schema.tablename. In this case, the sp is failing. If i put single quotes between the table name like 'schema.tablename' it is working fine. But is there any option to tweak the sp so that we dont have to pass the table name in quotes.

    The sp is

    create PROC [dbo].[sp_getrecords]

    (

    @TableName varchar(4000)

    )

    AS

    exec('select * from '+@TableName+'')

  • Your stored procedure should be working fine. The code below is working on my system.

    use AdventureWorks2008R2;

    go

    create PROC [dbo].[sp_getrecords]

    (

    @TableName varchar(4000)

    )

    AS

    begin

    exec('select * from '+@TableName+'')

    end;

    go

    exec [sp_getrecords] 'DatabaseLog';

    exec [sp_getrecords] 'HumanResources.Department';

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Hi,

    Thanks for the reply.

    Yes. the SP is working if we put single quotes across the tablename ('schemaname.tablename').

    I want to try whether is it possible to just pass the schemaname.tablename (without the single quotes) and make the sp to work. If it is possible, then we can add this sp to the keyboard shortcut and just select the tablename and press the shortcut to get the records.

  • any help on this??? 🙁

  • saravanakumar.G (8/28/2013)


    Hi,

    Thanks for the reply.

    Yes. the SP is working if we put single quotes across the tablename ('schemaname.tablename').

    I want to try whether is it possible to just pass the schemaname.tablename (without the single quotes) and make the sp to work. If it is possible, then we can add this sp to the keyboard shortcut and just select the tablename and press the shortcut to get the records.

    This doesn't make sense. The datatype being passed in is a varchar. In SQL you specify a varchar by putting characters between single quotes.

    Honestly I am not sure what you are trying to accomplish here. Typing "select * from" should be so second nature that you can type it nearly instantly. Then you have intellisense (even the default crappy one) is good enough to pull up table names fast enough. I hope this crazy sproc live only on your dev system and nowhere near production.

    _______________________________________________________________

    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/

  • What he's trying to do is set up a key code to run a procedure, so that he can highlight a section of text in a query and hit the key code to run the query with the selected text as a parameter. This is exactly the same as highlighting text and hitting <ALT-F1> to run sp_help on the selected text.

    Unfortunately, this feature doesn't add quotes around the selected text, so if you want to use this feature on more than just a base table, the text selected will have to include the quotation marks. Since putting quotation marks around the entire name, i.e. 'your_schema.your_table' instead of 'your_schema'.'your_table', will break anything but dynamic SQL even if your database is set up to use quoted identifiers, your desired functionality is not going to have much utility.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Here's an idea: let the proc get the schema name for you :-), then you don't even have to type it in at all!

    create PROC [dbo].[sp_getrecords]

    (

    @TableName sysname

    )

    AS

    declare @SchemaName sysname

    select top (1) @SchemaName = SCHEMA_NAME(schema_id)

    from sys.objects

    where

    name = @TableName and

    type in ('u', 'v')

    exec('select * from ['+@SchemaName+'].['+@TableName+']')

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (8/29/2013)


    Here's an idea: let the proc get the schema name for you :-), then you don't even have to type it in at all!

    create PROC [dbo].[sp_getrecords]

    (

    @TableName sysname

    )

    AS

    declare @SchemaName sysname

    select top (1) @SchemaName = SCHEMA_NAME(schema_id)

    from sys.objects

    where

    name = @TableName and

    type in ('u', 'v')

    exec('select * from ['+@SchemaName+'].['+@TableName+']')

    That works fine until you have a table with the same name in more than 1 schema and you want "the other one". 🙂

    _______________________________________________________________

    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/

  • Sean Lange (8/29/2013)


    ScottPletcher (8/29/2013)


    Here's an idea: let the proc get the schema name for you :-), then you don't even have to type it in at all!

    create PROC [dbo].[sp_getrecords]

    (

    @TableName sysname

    )

    AS

    declare @SchemaName sysname

    select top (1) @SchemaName = SCHEMA_NAME(schema_id)

    from sys.objects

    where

    name = @TableName and

    type in ('u', 'v')

    exec('select * from ['+@SchemaName+'].['+@TableName+']')

    That works fine until you have a table with the same name in more than 1 schema and you want "the other one". 🙂

    Yes, but nothing deals easily with that for-most-people unusual situation :-).

    One could extend the code to check for the current user's default schema, check it first, etc., but is it really worth the effort?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • DOH, just realized, the proc should check for an explicitly specified schema name and parse out the names before adding brackets:

    alter PROC [dbo].[sp_getrecords]

    (

    @TableName sysname

    )

    AS

    declare @SchemaName sysname

    set @SchemaName = parsename(@TableName, 2)

    set @TableName = parsename(@TableName, 1)

    if @SchemaName is null

    select top (1) @SchemaName = SCHEMA_NAME(schema_id)

    from sys.objects

    where

    name = @TableName and

    type in ('u', 'v')

    exec('select * from ['+@SchemaName+'].['+@TableName+']')

    go

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • saravanakumar.G (8/28/2013)


    any help on this??? 🙁

    Yes. If you insist on creating this proc, I recommend that you include a TOP 100 or TOP 1000 in the proc so that you're not using unnecessary resources. Using such a proc on big tables can really cause problems.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for all of your reply 🙂

    Yes. I tried to get the schema name for the table i pass and if there are more than one schema available(not many table name are available in more than one schema) then it will select the records from all the schema.

    The code is below

    create PROC [sp_getrecords]

    (

    @TableName sysname

    )

    AS

    BEGIN

    DECLARE @SchemaNames table(Row int identity(1,1), Sch sysname)

    DECLARE @i int

    DECLARE @SchemaName sysname

    DECLARE @STR nvarchar(2000)

    SELECT @i = 1

    SELECT @STR = 'SELECT OBJECT_SCHEMA_NAME(s.object_id,DB_ID('''+db_name()+'''))

    FROM '+db_name()+'.sys.objects s

    WHERE

    name = '''+@TableName+'''

    AND

    TYPE in (''u'', ''v'')

    '

    INSERT INTO @SchemaNames

    EXEC SP_ExecuteSQL @STR

    WHILE (@i <= (SELECT MAX(Row) FROM @SchemaNames))

    BEGIN

    SELECT @SchemaName = Sch FROM @SchemaNames

    WHERE Row = @i

    select @SchemaName

    exec('select * from ['+@SchemaName+'].['+@TableName+']')

    select @i= @i+1

    END

    END

    Any optimization of this script is welcome.. 🙂

  • Heh... all this work just to avoid typing "SELECT * FROM ", which can be a really bad idea in itself depending on the number of rows in the table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/30/2013)


    Heh... all this work just to avoid typing "SELECT * FROM ", which can be a really bad idea in itself depending on the number of rows in the table.

    I agree. My guess is this has become one of those projects where you start out with a goal in mind and even though it gets more complicated than it seems you just "have" to finish it. :hehe:

    _______________________________________________________________

    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/

  • Sean Lange (8/30/2013)


    Jeff Moden (8/30/2013)


    Heh... all this work just to avoid typing "SELECT * FROM ", which can be a really bad idea in itself depending on the number of rows in the table.

    I agree. My guess is this has become one of those projects where you start out with a goal in mind and even though it gets more complicated than it seems you just "have" to finish it. :hehe:

    Agreed... it's a little like gambling... "Just one more turn of the wheel and I..."

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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