sp_getrecords to get all records from a table
-
Hi,
this sp is helping me in development area in debugging the scripts to check the record count of a table on the spot. 🙂
-
There are better ways if all you need is the total record count of a table. Instead of returning all the data you could return just the number of rows (SELECT count(1) FROM table). This will prevent a lot of data being read and displayed on the screen.
Another way is to use the DMV's about the indexes. Read the rowcount from the HEAP (index_id = 0) or from the clustered index (index_id = 1). As far as I know this is the fastest way.
SELECTobject_name(object_id) as table_name
, row_count
FROM
sys.dm_db_partition_stats st
WHERE
index_id < 2
AND object_id = object_id('tablename')
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Sorry.. It was typo...
this sp is helping me in development area in debugging the scripts to see the data of a table on the spot. 🙂
-
saravanakumar.G (8/28/2013)
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+'')
You don't need a proc for this. If you map "SELECT * FROM " in the keyboard shortcut it will work as you want.
-
Sean Lange (8/29/2013)
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.
Wait, what's the non-default and presumably non-crappy Intellisense?
-
erikd (9/2/2013)
Sean Lange (8/29/2013)
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.
Wait, what's the non-default and presumably non-crappy Intellisense?
A third party Intellisense?
-
Thats very cool and the simplest way..... Thanks a lot mate.. You made my job into nothing 🙂
-
ApexSql do a free intellisense tool (and no, I don't work for them):
-
erikd (9/2/2013)
Wait, what's the non-default and presumably non-crappy Intellisense?
And now a message from our sponsor....RedGate has a product called SQL Prompt. I think may be a few others out there too.
_______________________________________________________________
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/
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply