Querying Tables Without Schema Prefix
Dec 27, 2006
Hi all,
I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.
However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)
I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.
Any ideas?
View 4 Replies
ADVERTISEMENT
Apr 18, 2006
Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?
For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**
Any help would be appreciated.
Please email me at dickson.lau@rogers.com
Thanks again.
Dickson
View 15 Replies
View Related
Apr 23, 2008
I think this should be a dead easy one.
SQL Server 200.
Can I query to find all the tables in a single schema? I can't for the life of me find this one out!
I'm currently using a dreaded cursor with an exists test to get the results I want but there is surely an easier way!
Cheers,
G
View 12 Replies
View Related
Nov 16, 2006
Hi,
I have created a new user in my database called "acm", I also created a new schema called "acm" as well, the "acm" user is its owner, and the default schema for "acm" user is the "acm" schema.
I also created a login called "acm" (surprise surprise) and set its default database to "mydb" database.
In "mydb" database I have a table "mytable" that is owned by "acm". In my connection string I am using "acm" as the user, but when I do:
"select * from mytable" I get error ("bla bla object bla bla"), when I use "acm" as a prefix for the table name it works fine... What do I miss??
My connection string is:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAccountManagerDb.mdf";Persist Security Info=True;User ID=acm;Password=acm;Connect Timeout=30;User Instance=False
Thanx,
Yuval.
View 7 Replies
View Related
Aug 21, 2014
When I create a table in sql server database name appears in prefix of table.
My database name: Digitall
My table name: Digitall.News
How can I remove Digitall prefix from my tables?
View 1 Replies
View Related
Nov 16, 2015
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
 1, View1, Column4
 2, View2, Column4
 2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
View 3 Replies
View Related
May 23, 2008
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
View 3 Replies
View Related
Dec 13, 2007
Hi all,
lets say i have a table as follows
id name1 red2 orange3 green
and this has s join to another table via the id
table2
id event 1 stop2 stop3 start
now if i make a view and drop these two tables the jkoin is there but i would liek to report on all names who have a event of "go"but it gives me no results , which is right . but how can i output all the names and in the evnet put a empty SO
name eventred emptyorange emptygreen empty
thanksrobby
View 5 Replies
View Related
Jul 20, 2005
Hello,I am quite new to ms-sql and I have a problem : I want to create an SQLrequest which would copy serveral records from a table in a given databaseto another table (with exactly the same structure) in another database(theses two tables and databases already exist).Could you please tell me how to do this ? I dont know how to access twodifferent databases in a single SQL request.Thank you for you help.
View 3 Replies
View Related
Nov 15, 2006
Hi all,
How do I query two tables in different databases on the same SQL Server?
In short, I want to do:
Select A.*
from database 1. table 1 as A
inner join database 2. table 1 as B
on A.COL1 = B.COL1
Both database 1 and 2 are on the same SQL Server.
Please advise.
Thanks,
V
View 4 Replies
View Related
Dec 8, 2005
I get a 90-120 second blocking when send 15 or so simultaneous queriesto SQL Server 2000 that query a view made up of two joined tables.After each query is blocking for the same amount of time they allreturn. Further identical queries of this type work in 3-4 seconds(caching?) until hours later where it happens again. If I query thetables directly (without the view) I still get the same blocking. If Iremove the join (it is a simple inner join on two columns) I do not getthe blocking.Any ideas?
View 3 Replies
View Related
Jan 18, 2006
This seems like a very simple question but i have never been able tofind an easy answer to it.I have a user table and i do a join with another table, we'll call theother table a results table.The results table has numerous rows with the userid foreign key.I want to make a query that will give me the number of rows in theresults table for each user where the result is some valueThe query is simple to make but will only show the users who have arecord in the results table the meet the where criteria, however i wantto display each user and show a record count of 0 when there are noresults in the results table that match the criteria.for example i have 2 tables.tblUsers_______________userid | username--------------------------1 | user12 | user2tblAnswers________________userid | answer----------------------------1 | 11 | 01 | 42 | 12 | 0if i run the query:select max(username), count(answer) from tblUsersleft outer join tblanswers on tblAnswers.userid = tblUSers.idwhere tblAnswers.answer = 4group by tblUsers.idi just getuser1 | 1i want to getuser1 | 1user2 | 0the only way ive found to do this is with a temp table and a curser tocreate all the users records and go back through an insert the answercount for each user. This approach seems very expensive and requires aquery that is 3 times larger than is needed for the same resultswithout including 0 count records. I know there must be a better way todo this.Any help is appreciated.
View 2 Replies
View Related
Jan 4, 2004
I am trying to query multiple tables that have the same column. They are a supposed to be a unique ID to the product, but not to the database. I have a primary key already set to each othe tables. The tables do not share anything between them, but i would like to search for these IDs in other tables before my insert command is executed. I am writing my code in VB. example of what i am trying to accomplish.
Table1 = TVs
ID/SKU_ID/Manufacturer
1 87654 Samsung
Table2 = DVD_Players
ID/SKU_ID/Manufacturer
2 765234 Kenwood
I want to search for SKU_ID in multiple tables( I think a total of 9 tables). I want to search between tables to catch for the same SKU_IDs. and if it finds it, raise error. I will be able to figure the vb.net portion. I just dont know how to go about the sql query portion. Thanks in advance and appreciate any help. Thanks!
View 2 Replies
View Related
Jun 7, 2005
What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are
pretty complex so I created each query as a Stored Procedure. These queries are
dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the
current user's username as a parameter. Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory. Each of the sub stored procedures all
have the same columns. After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable. I then bind the DataTable to a
Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I
tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource? Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled. I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly. But the query in one of the
sub SP is a normal but complex select.
View 5 Replies
View Related
Jul 23, 2005
Hi;I am looking for suggestions about how to solve a problem using tsql.I have been asked to create a report concerning 4 tables.Each of the 4 tables is in its own database.The 4 tables are identical in name and structure.I would like to query all 4 of these tables and get the results as ifthey were one table.All suggestions welcome, thanks.Steve
View 2 Replies
View Related
Jul 20, 2005
Hi, all:This is probably a simple problem, but, as an SQL newbie, I'm having alittle trouble understanding multi-joins and subqueries.I have the following tables and columns:MemberTable-----MemberID (primary key)MemberNameAddressCountryFoodsTable------FoodID (primary key)FoodNameMusicTable-----MusicID (primary key)MusicNameMoviesTable-----MoviesID (primary key)MoviesName....and their linking tables...Members2FoodsTable-----MemberID (foreign key)FoodsID (foreign key)Members2MoviesTable-----MemberID (foreign key)MoviesID (foreign key)....and so forth.Now what I'm trying to do is retrieve a specific MemberID, his address info(from the Members table), and get a listing of his favorite Movies, Foods,Music, etc. I know I probably need to JOIN tables, but where do I JOIN thetables? Do I have to JOIN the various Music/Foods/Movies tables or is itthe Music/Members2Music and Foods/Members2Foods, etc. tables? And I assumeI would probably need to perform a subquery somewhere?I realize I'll need to first filter the Members, Members2Music,Members2Foods, etc. tables by the MemberID, and afterwards, retrieve alisting of the actual Music/Foods/Movies names. I'm just confused how to dothat. (By the way, I have a total of 10 other tables or in addition toMusic, Foods, etc. so it's a lot of table JOINing.)If someone could please help me out with the actual SQL coding, I'd reallyappreciate it!Thanks for the help!J
View 6 Replies
View Related
Mar 15, 2008
Please, could anyone tell me how to get information from which attribute of which table is the attribute from the view derived(it could also be a complex expression, not just attribute)through querying system tables or views(INFORMATION_SCHEMA, preferably if possible, because it's standard)
i'm using MSSQL2005.
Thank you!
View 4 Replies
View Related
Sep 3, 2007
I am learning T SQL and SQL queries and have limited VB knowledge, and have a some simple queries to run on a table with parameters, and would like verification of the proposed methodology and suggestions.
Simply put, I have a [Transactions] table with columns [Price], [Ticker], [TransDate], [TransType] and calculated columns for [Days] and [Profit].
There are two parameters, [@Dys] (to query a the table for transactions within a certain period[Days = 30] and [@TT] to query only the closed transactions ie... [TransType='C']
I have been studying Stored Procedures and will be writing a Stored Procedure, but need verification if the following will work... Getting the SUM and AVG calcluations for the fields above is not a problem but I need to display SUM and AVG information also for those transactions where [Profit >0] and [Profit <0], which is easy enough by creating a subquery. But the problem is:
1. If I use a SubQuery for [Profit <0] and for [Profit>0], can I create an alias for [Count(*)] (to get a row or transaction count for each, and then divide that into the Total [Count(*)] alias for the Transactions table to get a value for % profitable or Probability (% total Profitable trades versus % total Unprofitable trades)?
2. Or, do I need to create either temporary tables or views to have 3 distinct tables (1 table for Transactoins and 2 temp or Views for [Profit >0] and [Profit <0])?
Any suggestions and advice or examples on how to do this would be appreciated.
Craig
My questions are:
View 2 Replies
View Related
Nov 8, 2006
I need help in writing a query.
The query should get top 10 items and their values from current year and the values for the same items from previous year table.
I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.
The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.
I am attaching some images below to give more information.
Image of results from my query.
Image of how the final output should look like.
The Store Procedure code is:
ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'
EXEC sp_executesql @SQuery
END
View 2 Replies
View Related
May 14, 2008
hello,
I have list of tables in a database where schema1 is attached. when ever i login thru windows authentication or any other user and if i run select * from schema1.table i get proper values.
what i need is even if i login thru windows authentication i should be able to run the query without attaching the schema
i.e., select * from table should work. i am not sure what permissions has to be given.
pls advise.
View 6 Replies
View Related
Oct 6, 2006
In the process of purging data to history tables,
we wanted to make sure that no schema changes have been done
to the main or the history table.
So to ensure identical schemas, we use this function:
ALTER FUNCTION dbo.fnCompareTableSchema
(
@t1Name NVARCHAR(257)
,@t2Name NVARCHAR(257)
)
RETURNS BIT
AS
/*
Compares the schema of 2 tables
If the schema is different RETURNS 0
If the schema is identical between the two table, RETURNS 1
NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1)
==================================================================================================================
SAMPLE USAGE:
DECLARE @schemaOK BIT
SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')
IF @schemaOK = 1
PRINT 'TABLE SCHEMA IDENTICAL'
ELSE
PRINT 'TABLE SCHEMA DIFFERENT'
==================================================================================================================
*/
BEGIN
IF @t1Name = @t2Name
RETURN 1
-- check if schema is different
IF EXISTS
(
SELECT*
FROM
(
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1)
UNION ALL
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1)
) U
GROUP BY
COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
HAVING COUNT(*) <> 2
)
RETURN 0
-- schema identical
RETURN 1
END
View 6 Replies
View Related
Oct 11, 2006
Hello
in my database I have schema "x". I have user "x" who is database owner, The user has schema "x" as default schema and is owner of this schema.
Now there are tables "x.mytable"
If I connect to the db as "x" and try
select * from mytable
I get an error "Invalid object name", I have to write it this way
select * from x.mytable
Why?
Thank you
Eckard
View 5 Replies
View Related
Jan 9, 2006
Hi guys, please help.What I did is :Step1. create a Database named [Test], it's default owner is [sa]Step2. create a User in Database [Test] named [kimliuTest], which mapsto the Login [kkhadkimliu] on this Database Server, it's a windowsdomain user.Step3. create a Schema in Database [Test] named [schTest], whose SchemaOwner is User [kimliuTest]Step4. created two tables: [dbo].[t1] and [schTest].[t2]Step5. run the scriptALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTestto set the default schema.Now my problem is, when I try to runselect * from schTest.t2, everything is OK, but if I runselect * from t2the error message says:Msg 208, Level 16, State 1, Line 1Invalid object name 't2'.why? I'm loggin in with my [kkhadkimliu] windows domain id , ActiveMonitor also shows that. but the command does not work?Yeah, it's a good habit to write the full name of the Table with Schemaname as prefix, however, we did our development in SQL 2000 and tryingto upgrade to SQL2005, so trying to avoid scanning codes and insertingin thousand of places.How could we omit the Schema name when referrring to Tables? Thanks.ps. I posted in another topic days ago, but nobody is replying, when Itried to bring it forward by replying myself, it failed. please help me, thanks!
View 3 Replies
View Related
Sep 12, 2006
Hi,Is there a way I can change schema name on tables and stored procedures? How do I do this?I´m very news to SQL and .netThanks
View 1 Replies
View Related
Apr 30, 2008
Hi all,
does anyone know of a way in which you can backup and restore objects that belong to a specific schema?
So in this example, I€™d want to only back up and be able to restore the tables that belong to schema 1, leaving Schema2 stuff alone.
--DB
----Schema1.Table1
----Schema1.Table2
----Schema2.Table1
----Schema2.Table2
Thanks in advance,
Andy.
EDIT: I forgot to add that I want to backup the tables and the data contained within.
View 4 Replies
View Related
Dec 17, 2005
SQL BPA says the following:"One or more objects are referencing tables/views withoutspecifying a schema! Performance and predictability of theapplication may be improved by specifying schema names.""When SQL Server looks up a table/view without a schemaqualification, it first searches the default schema and then the'dbo' schema. The default schema corresponds to the currentuser for ad-hoc batches, and corresponds to the schema of astored procedure when inside one. In either case, SQL Serverincurs an additional runtime cost to verify schema binding ofunqualified objects. Applications are more maintainable andmay observe a slight performance improvement if objectreferences are schema qualified."How important is to specify the schame (dbo. in my case) instored procedures? Will it really improve performance if I goand fix each object that is missing "dbo."?The problem is I have thousands and thousands of themwith no schemas. Before I invest a lot of time fixing themI am trying to determine if it's really worth it or not?Thank you
View 1 Replies
View Related
Sep 10, 2015
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
View 3 Replies
View Related
Feb 20, 2007
Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go
This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:
Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.
How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?
View 1 Replies
View Related
Jul 11, 2014
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
View 1 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
May 31, 2007
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
