sql server - T-SQL: Access DB objects in same schema without qualifying? -
i have project many stored procedures, functions, views , tables.
i have project deployed within database within same schema. example we'll call "mainproject"
now, i've been tasked creating duplicate project in same database. obvious solution clone objects schema. i've been able using sql scripts. have schema called "secondproject" containing objects first schema.
the problem is, in of functions, views , procedures, original schema referenced, e.g. declare @somevar int = mainproject.getid(@someobject), select somecolumn mainproject.sometable, update mainproject.sometable set somecolumn = @somevalue someothercolumn = @someothervalue executing functions ends referencing incorrect schema.
going through hundreds of views, procs , functions find , replace occurrences of schema mainproject tedious , potentially unreliable process.
my question is, there way specify "within current schema" when referencing object?
just doing declare @somevar int = getid(@someobject) results in 'getid' not recognized built-in function name. , can't save procedure.
edit: first answer below helps shed little light on situation. if don't specify schema on access object assumes it's in default schema.
the problem on production server against running ddl script, server's default schema user account dbo. application user has access dml statements cannot use account run create script.
i suppose if there's no way have stored procedure assume unqualified objects in same schema resides in, i'll have resort find/replace after generating script....
in sql server, you're talking schemas. if none specified, database engine default whatever current user's default schema is. default, that's dbo. in short, if have objects same name in different schemas, you'll need specify schema want use in query.
Comments
Post a Comment