Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

sp_getrecords to get all records from a table Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 12:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:09 PM
Points: 45, Visits: 141
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+'')
Post #1489023
Posted Wednesday, August 28, 2013 12:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 2,449, Visits: 2,993
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’! **
Post #1489026
Posted Wednesday, August 28, 2013 12:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:09 PM
Points: 45, Visits: 141
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.
Post #1489028
Posted Wednesday, August 28, 2013 11:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:09 PM
Points: 45, Visits: 141
any help on this???
Post #1489521
Posted Thursday, August 29, 2013 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489724
Posted Thursday, August 29, 2013 1:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1489887
Posted Thursday, August 29, 2013 3:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 2,268, Visits: 3,429
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1489916
Posted Thursday, August 29, 2013 3:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489920
Posted Thursday, August 29, 2013 3:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 2,268, Visits: 3,429
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1489933
Posted Thursday, August 29, 2013 3:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 2,268, Visits: 3,429
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1489942
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse