August 28, 2013 at 12:17 am
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+'')
August 28, 2013 at 12:35 am
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';
August 28, 2013 at 12:50 am
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.
August 28, 2013 at 11:15 pm
any help on this??? 🙁
August 29, 2013 at 7:48 am
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/
August 29, 2013 at 1:55 pm
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
August 29, 2013 at 3:10 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2013 at 3:16 pm
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/
August 29, 2013 at 3:35 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2013 at 3:55 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2013 at 8:27 pm
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
Change is inevitable... Change for the better is not.
August 30, 2013 at 1:19 am
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.. 🙂
August 30, 2013 at 7:41 am
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
Change is inevitable... Change for the better is not.
August 30, 2013 at 7:45 am
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/
August 30, 2013 at 7:55 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply