December 21, 2012 at 4:59 pm
We have a script that we run on various different databases of customers.
so for example it may have a line like this
select * from [dbo].[table_name]
Most of the times its fine since most of our customers use the default schema dbo. Few of the customers like to alter the default schema and as a result the ownership of the tables changes, so our script fails.
We usually manually change the script by replacing [dbo] with their schema.
We would like to make our script smarter by just query schema_name() and prepending the result to the table_name.
so something like
select * from [Schema_name()].[table_name]
I know this probably comes into the territory of dynamic sql, I was wondering if someone could show me an easy way to do this.
thanks.
December 24, 2012 at 12:21 pm
Trying something like this, one can get schema_name prefixed dynamically
Declare @sc nvarchar(500),
@sql nvarchar(4000),
@parmDef nvarchar(500)
select @sc = (select schema_name());
--set @sql = N'select * from ' + '['+ @sc +']' + '.add_yourtable_name'
--set @sql = N'INSERT INTO [your_dbname].'+ '['+ @sc +']' +'.[add_yourtable_name'] ([add_yourcol]) VALUES (''boom'')'
-- set @sql = N'DROP TABLE [' + @sc + '].[add_yourtable_name']'
set @parmDef = N'@sc nvarchar(500)';
Execute sp_executesql @sql, @parmDef, @sc;
December 24, 2012 at 5:46 pm
Why not just drop the schema name entirely from the query?
Or query the schema name ahead of time and then conditionally execute the query, either with or without the dbo. prefix using an IF statement at each point where a schema-qualified prefix is used.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 25, 2012 at 12:38 pm
Is there a reason you're avoiding dynamic SQL? That seems to be the easiest solution.
If you go through sys.objects, that will give you a list of objects by type in the database. You can filter for the type of object (for example, table or stored procedure). That particular system table has a [name] field, and you can use object_schema_name([object_id]) to return the schema prefix.
Another table you can query is sys.indexes. That will give you a list of every indexed object (so tables and indexed views), and depending on whether the table has an index with an id of 0 or 1 depends on whether it has a clustered index or is stored as a heap.
One trick I've learned when constructing dynamic SQL like this is to put square brackets (or double quotes) around the object names when I build them. That takes care of any objects that have spaces or weird characters in the names.
If you wish to avoid dynamic SQL, you can always write some SQL that queries the system objects to create the SQL you need, and then save that output to a file. However, you would need to re-run that every time a change is made to any of the objects in the database.
December 25, 2012 at 6:27 pm
Bruce - I love your avatar. Looks like one of my cats, except his eyes are green.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 2, 2013 at 12:56 pm
Back from holidays, Happy new year first.
I tried that during my calls with customer but that did not help. Customer had a schema name of the format 'xyz/zyx', and i tried running our queries without any schema name prepended to a table name but it would fail. I had to actually do this
[xyz/zyx].<tableName>, even trying xyz/zyz.<table_name> would not work since they had a 'slash' in their schema name.
January 2, 2013 at 1:04 pm
Well , its not that simple for us. We use a product called DeltaSql to generate these sqls. I have no control and no knowledge of that product. On top of that I am peripherally involved in the whole process. So the challenge was to provide the in house expert of DeltaSQl a simple solution. So I did the R&D on it, now going forward it remains to be see how this can be incorporated into DeltaSql while it generates the udgrade sqls.
I agree the sys.objects and other tables would help but given the parameters that I have I am not yet sure if that is an easy and simple solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply