Can't Get The Complete List Of Tables Owned By The Dbo
Oct 10, 2005Hi,
View 12 Replies
ADVERTISEMENT
How To Find Out The List Of All Tables And Views Owned By A Particular User In Any Database?
Oct 15, 2001How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.
View 2 Replies View RelatedMultiple Tables To Create A More Complete List
Jun 26, 2014My SQL knowledge is limited to only trial-and-error and on-the-fly learning, sadly. However, I've had past experience with VB and some earlier, more basic programming, so I don't find it that difficult to grasp; it's just rather overwhelming without any formalized training.
I'm trying now to solve a problem that involves a sales history report. Our sales history table lists only records for product lines which a customer has purchased in the past. However, the budgeting team needs a list of each customer with all product lines, even if they've never purchased, and they'd like to include an industry code value that is from another table.
So the way the tables are looking is this:
Sales_History as the main table with the appropriate data in it
Product_Lines as a reference to fill out the main table with non-purchased lines
Accounts as another reference that gives the industry code per the customer ID in the main table''I'm seemingly on the right track with the following, which tries to create a list of all product lines and the records from sales_history which match them:
SELECT sales_history.sah_sort_id,sales_history.sah_sortkey3,sales_history.sah_sortkey2_desc,
prod_lines.pl_prodline_id,prod_lines.pl_short_desc
FROM dbo.sales_history
INNER JOIN prod_lines ON sales_history.sah_sortkey3=prod_lines.pl_prodline_id
[code]...
However, this produces a list of only 21 rows, when there should be 41.
Dbo Owned Stored Procedure Hitting Another User Owned Table
Aug 13, 2007I am trying to come up with a solution that does not involve having a version of every stored procedure for every user I have...
Here is the problem...
I am going to have multiple users that need to have their own "product table". The structures are going to be the same for all. We currently only have one user and it is a DBO... all stored procedures are dbo.[sp name]... is there any way to get it so that the product table in the SP will be the user owned product table and not the dbo table??
I have tried just taking out the dbo prefix with no luck... the user's default schema will match the table they own so when they do a straight select they get the right information but it is just the SPs that I can't seem to get to work...
The only thing that I have come up with is making the SPs dynamic with having the username as parameter.
Is there anything else I can try??
and SQL 2005 SP2 on Win 2003 SP2
Complete List Of Error Codes
May 12, 2006Does anyone know where to obtain a complete list of error codes which can occur in SQL Server ? For example : errror number 547 is a foreign key violation - where can I get a complete list of these numbers and there descritions ?
I can't find it in BOL or anywhere
SQL Server 2008 :: List Of Commands That Require Exclusive Access In Order For Command To Complete
Aug 10, 2015Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.
View 0 Replies View RelatedScheme Login Can't Access Tables Owned By Scheme
Dec 4, 2007Hi,
I've having some baffling problems with a java applicaiton!
I have an application server called WA01 which used to access two tables on an MS SQL 2000 server via a scheme login. The two tables are:
_PurchaseOrderInterface
_PurchaseInvoiceInterface
Both tables were owned by the scheme user, with enough permissions to read & write. The java application on server WA01 could happily read the data within the tables and write a bit flag back to each row.
The MS database has been moved to a new server which no longer allows the java application on server WA01 to access the tables via the scheme login, the only way the java app can view and update the tables is by changing the owner of the tables to dbo. The new server is still MS SQL 2000, with comparible security settings.
The java app keeps complaing of an unknown source when trying to access via scheme, is this a domain trust issue between the two servers? Any ideas would be welcomed. I'm not an SQL expert but have a good grasp of the security structure etc.
Regards,
Alan
Conceptual Ideas - 2 Tables One Changes Other Complete Cursors?
Jul 23, 2005I think cursors might help me, but I'm not sure. I'm looking for ideason how to solve a problem I have.Consider two tables, one table contains student information (very wide100 fields) , the other historical changes of the student information,(narrow, just fields that record changes).As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,RECORD_DT and has one student in it.Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2records, since the student changed their major 2 times.I want to end up with a table the contains 3 rows, the 2 changes to theMajor and the current student record. I want each row to be complete.Everything that I have tried (joins, outer joins, union) I end up withsome field being null (in my example, the STUDENT_NAME would on be inthe original row, and null for the two changes)I know this is pretty vague, but I am wondering if this is a place touse CURSORS?(Some of you may recognize this as a type 2 dimension or slowlychanging dimension as used in a data warehouse, which it is. I need tobuild up my historical changes to I can feed it to my warehouse. I havethe current student record, and all the descreet changes made to thestudent.)TIARob
View 18 Replies View RelatedRecommended Owned Of A DataBase
Jan 18, 2002Hi,
What are current thoughts about who should own a Database?
I see 3 possibilities:
1. The DOMAINAdministrator (person wo starts up the Server at Bootup)
2. 'sa', or
3. a person/user closely tied to the database.
reasons for each?
Thanks for your opinions.
MichaelG
Can't Connect To Any Database Not Owned By ServernameASPNET
Feb 9, 2007I'm developing an intranet application and am using the Express versions of VWD and SQL Server 2005 for initial development before obtaining full licensed versions.
Database connections work fine when testing from within VWD but when testing from browser, unless the database owner is the IIS servernameASPNET, there are various errors depending on the specific connection settings, i.e.
Login failed for user ''. The user is not associated with a trusted SQL Server connection. -
Receive this error when a GridView is connected to a remote SSE db.
---
SELECT permission denied on object 'Table_1', database 'TEST', schema 'dbo'.
Receive this error when GridView is connected to local SSE db not owned by servernameASPNET.
---
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
This happens when using any control that is connected to SSE db created in VWD and stored in App_Data folder (owner is the user logon name)
---
Any help or ideas would be appreciated. I want to have all local data stored in App_Data folder.
Thank you.
Look Up For DB Owner And Return Names Of DB's Not Owned By 'Sa'.....
Oct 10, 2001Hi,
I need to write a stored procedure,that look's up the owner of each database on an server and returns the names of the databases which are not owned by SA.
Can we do this..if so how??
TIA
Create DB Role Owned By Public
Jul 20, 2007I use the following script in order to create db role:
USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [public]GO
It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.
However this code works fine:
USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [dbo]GOALTER AUTHORIZATION ON ROLE::[myRole] TO [public]GO
So the question is why?
Making Restricted User To See Only The Database That Is Owned By It.....!
Feb 7, 2008How would I hide the sa account to be accessible under Logins for a restricted User in sql server 2005 and also other folders like Replication, Managment, etc need to be hidden too.
any idea,
thank,s
How Do I Get A List Of Tables In T-SQL
Mar 11, 2005Hi,
Is there anything equivalent to Oracle's Select * from tab in MS SQL.
How I Can Get List Of Tables?
Dec 24, 2005Hi friends,
How I can get list of tables and list of fields within those tables in SQL server.
Thnak a lot.
List Of Tables
Jun 6, 2001How to obtain the list of the tables of a base by un script
View 1 Replies View RelatedHow To Get A List Of Tables Currently In A DB
Mar 21, 2001Hi all,
Does any one know what command(s) I can use from a SQL prompt to tell me what tables are cruuently residing in a particular database. I'm looking for something along the lines of "select list_of_all_tables from tempdb", which can be applied to any valid db on the system.
Thanks in advance for any guidance.
Rgds,
Kevin
List From Three Tables
Oct 5, 2006HI I have three tables as here:
inventory
isbn, status, status_date, quantity
authors
id, first_name, family_name
books
isbn, category, title, num_pages, price, copyright, author1, author2, author3
How can I list title and author name of the book that is most in stock?
My another problem to
Listing names of authors who have not written ay books
Thanks folks!!!
List All Tables Ans SP
May 25, 2008Hi
can I through a script retrieve a list af all tables and stored procedure in a database?
List Tables Which Are Used By A SP
Sep 17, 2007Hi,
I got a request from a devloper asking, how can he find out what tables his stored procedure his using, so he can remove unwanted SP which are using old tables in the database.
Bharath JrDBA
List Of Tables In A Db
Jul 20, 2005Hi Allcan some one please tell me the transact sql code that can list all tablesin a db.thanksTodd
View 1 Replies View RelatedProper Syntax For Adding Object Name Owned By Domain User
Aug 11, 2005I am receiving an error based on my query that is giving me the following error:
Code:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Config'.
I'm assuming that this is because the table was not made by dbo, but my domain account.
The query is currently defined as:
Code:
SQL = "SELECT * FROM Config"
but I'm assuming it needs to be defined with the syntax of ownername.objectname. My problem is when I change the string to this:
Code:
SQL = "SELECT * FROM sanm.APHinzeE.Config"
I get this error:
Code:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.
I tried to escape the slash using / but then I get the error on that. How do I specify my account name with the slash?
Retrieving A List Ot Tables
Mar 7, 2007Hello Everyone and thanks for your help in advance. I am working on an application that connects to SQL Server. I need to find out if there is any way (I know there is, not sure how) to retrieve a list of tables within a database and also, a way to retrieve a list of databases within a server. I am using VB.Net in a web application. Any help on this owuld be greatly appreciated.
View 2 Replies View RelatedHow To Get List Of Tables In A Database
Jan 22, 2004Hello
How I get the list of tables in a database. I'm using sql server 2000.
Thanks
List User Tables
Apr 14, 2001I'm brain-dead today, sadly. If it weren't for IE remembering previous entries, I don't know if my name and email would have made it into the header correct :-)
I want the SQL command that lists the names of all user tables.
Alternatively, I have the following problematic Access 2000 code:
Public Sub ListAllTables()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim cnn As New ADODB.Connection
Dim i As Integer, j As Integer
Dim vgRet As Variant
Dim intPrefixLen As Integer
Dim strAppend As String
DoCmd.Hourglass True
cnn.Open CurrentProject.Connection
cat.ActiveConnection = CurrentProject.Connection
intPrefixLen = Len(CON_pkgPrefix)
Debug.Print cat.Tables.Count
For j = (cat.Tables.Count - 1) To 0 Step -1
Set tbl = cat.Tables(j)
With tbl
Debug.Print tbl.Name
vgRet = SysCmd(acSysCmdSetStatus, tbl.Name)
End With
Next
Set tbl = Nothing
Set cnn = Nothing
Set cat = Nothing
DoCmd.Hourglass False
vgRet = SysCmd(acSysCmdSetStatus, " ")
End Sub
This code runs fine against an MDB but against SQL it includes all the views, rather than just the tables. If you have a fix for this, that will do just fine!
Arthur
List Of All Tables And Size
Oct 1, 2001Does anybody know how to make a query that presents ALL
tables and their sizes (in Kb or Mb) from a database?
Is there a Stored Procedure for this purpose?
Thanks
Cannot List Tables Or Other Objects
Nov 16, 2004Sometimes, when a try to list tables or view (or access Management, etc) Enterprise Manager hungs up.
Using SELECT * FROM SYSOBECTS also freezes the process. But the SQLServer is operational for data updates and retrieves.
Anyone?
thanx!
List Tables In A View??
Jan 5, 2005Is there a way to easily list the tables/views that a view is using to get its data?
Thanks in advance,
Shawn
How Can I List The Tables If I Have SA Access?
Apr 3, 2008Hi,
I'm very new in SQL Server. Please help me to combine the connection string to the server.
62.33.197.7
login: sa
pwd: S#15trLdatabase
What would be the connection string then? And can I get the tables names if I have the connection string?
Get Value From Columns Using List Of Tables
Feb 5, 2014how do I get value from column using list of tables?
For example, I have list:
schema_name, table_name, column_name
How do I get:
schema_name, table_name, column_name, column_value
How To Get Distinct List From Two Tables
Feb 28, 2014// C - Categories, P - Parameters, CP_Link - linkage table between C and P
// Basically, it is a list of features belonging to a particular category.
DROP TABLE [C];
CREATE TABLE [C] (
ID_CAutoinc,
Name varchar(32),
PRIMARY KEY( ID_C )
[code]....
OK, this works fine, but I want to get a list of parameters (from P table) which holds only those items that linked to all categories requested. In other words, I want only 'leg' and 'hand', but not the 'beard', because 'girls' not linked to 'beard'.Only common features for all of categories listed "IN (,,,)" wanted.
List All Tables In Master
Jan 23, 2007Hi,I need to list all the tables in northwind database.I'm using sql server 2000.In query analyser i'm in master database.I don't want to change the database to northwind.How to list all the tables in northwind database when the databasecombobox has master in query analyser.I want to run this query with database name.I won't want to run forcurrent database.Kindly help me to solve the problem.Thanks & Regards,Mani
View 1 Replies View RelatedHow Do I List Columns Within Tables?
Mar 4, 2008Hi All.
I'm an Oracle DBA who's currently being asked to look at a SqlServer Database. I need a list of columns per table, but am having trouble.
I'll admit I might be being lazy here, but I'm in a hurry and using the valueable resources available to me!! Would really appreciate the sql i need to copy into the query window. Much obliged!!
I need........
Table A
Column1 Datatype
Column2 Datatype
Table B
Column1 Datatype
Column2 Datatype
etc....
Many thanks.