SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_getrecords to get all records from a table


sp_getrecords to get all records from a table

Author
Message
saravanakumar.G
saravanakumar.G
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 215
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+'')
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8884 Visits: 3718
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’! **
saravanakumar.G
saravanakumar.G
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 215
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.
saravanakumar.G
saravanakumar.G
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 215
any help on this??? Sad
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64774 Visits: 17979
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.

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)
drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15955 Visits: 11224
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20334 Visits: 7427
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64774 Visits: 17979
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". Smile

_______________________________________________________________

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.

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)
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20334 Visits: 7427
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". Smile


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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20334 Visits: 7427
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search