Dynamically adding the schema name or populating the schema name

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Dynamically adding the schema name or...

Dynamically adding the schema name or populating the schema name

Rate Topic

Display Mode

Topic Options

Author

Message

soodrah

Posted Friday, December 21, 2012 4:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 07, 2013 7:33 AM
Points: 4, Visits: 10

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.

Post #1399633

soodrah

Posted Monday, December 24, 2012 12:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 07, 2013 7:33 AM
Points: 4, Visits: 10

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;

Post #1399965

dwain.c

Posted Monday, December 24, 2012 5:46 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,345, Visits: 3,191

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1400006

Bruce W Cassidy

Posted Tuesday, December 25, 2012 12:38 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

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.

Post #1400092

dwain.c

Posted Tuesday, December 25, 2012 6:27 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,345, Visits: 3,191

Bruce - I love your avatar. Looks like one of my cats, except his eyes are green.

Post #1400112

soodrah

Posted Wednesday, January 02, 2013 12:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 07, 2013 7:33 AM
Points: 4, Visits: 10

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.

Post #1402052

soodrah

Posted Wednesday, January 02, 2013 1:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 07, 2013 7:33 AM
Points: 4, Visits: 10

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.

Post #1402054

« Prev Topic | Next Topic »

Permissions