User Tables Really Accesed By SQL

Jul 6, 2006

I administre a DB that has about 2000 user tables, unfortunatly I haven´t documentation, and the people that create thouse tables are no more in the company.

How can I identify the user tables that really are used by SQL today?

I used the Profiler to identify the Stored Procedures that are called recently, but when I try to use Object:Opened event class to look the tables that are opened, it dosn´t display anything.

I hope you can help me.

View 4 Replies


ADVERTISEMENT

How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?

May 22, 2008



Hi,
Please give the T-SQL script for this ? Thanks

Shanth


View 4 Replies View Related

SQL 2012 :: How To Grant A New User Same Direct Privilege To Tables As Existing User

Feb 18, 2014

I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.

View 2 Replies View Related

How To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?

Aug 12, 2006

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

View 6 Replies View Related

List User Tables

Apr 14, 2001

I'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

View 1 Replies View Related

How To Copy All The Tables Into The Same Db With A Different User Name?

Nov 2, 2001

I need to duplicate all the tables owned by dbo to another object ownername in a database within the same database.That means,if there are 100 table owned by dbo in a 'Database A'. I need to have a total of 200 tables in the same database 100 each for dbo and 100 for another user.They should have same schema including the keys etc and data.How is this possible?Through DTS we can't copy them to the same databse.We do not want to have a temp db inbetween to complete this process.Any ideas on how to do this? NOTE:This is not a one time job.
Thanks.
Sheila.

View 1 Replies View Related

Look Up User Permisson Of All Tables

Mar 15, 2000

There are more than 20 tables and 100s of users access the database.
what is the quickest way check what permission users have on the tables. Could anyone help me

View 1 Replies View Related

Filter Tables According To User

Aug 29, 2006

Hi,

Im new to SQL Server and Im having trouble to decide wich is the best way to filter the tables that one user can read and write.

For example the table Sales as a field named Branch (SMALLINT) with values 1 or 2 (the store that made the sale). User "A" can see and write only records where Branch = 1.

Im developing a windows program that shows all the purchased orders in a DataGridView. So should I make the filters (permissions) in the program or in the SQL Server?.

I hope I made my self clear (my english is not so good)

View 5 Replies View Related

Query To Retreive User Tables In Sql Db

Jan 2, 2004

Is there a way to create a query that will return all user tables inside a sql db

Thanx

View 4 Replies View Related

Calculating Rowsize For User Tables

Aug 6, 2001

Hi,

how do i calculate rowsize for user tables in a database

Thanks in advance.....
jfk

View 3 Replies View Related

JDBC API To Fetch Only User Tables

Apr 11, 2006

Hi,

I am using JDBC DatabaseMetaData's getColumns() to get the table names, column names and datatype of the columns. As a result, I am getting user tables as well as the system tables. How to fetch only the user tables excluding the system tables like sysindexes, etc. I know this is possible by executing a select query (using SYSOBJECTS and xtype = 'U'). But, is there anyway to fetch only the user tables using the databasemetadata API.

Please advice,

MiraJ

View 1 Replies View Related

Find The List Of Tables For A User?

Feb 18, 2015

how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.

View 1 Replies View Related

Size Of User Tables In A Database

Nov 4, 2003

The following procedure will display the size of all the user tables in a database.

CREATE proc sp_tablesize
as
if exists (select * from sysobjects where name = 'sp_tablesize')
begin
goto calculate_tablesize
end
else
begin
CREATE TABLE #SpaceUsed
(
TableName sysname,
TableRows int,
TableSize varchar(10),
DataSpaceUsed varchar(10),
IndexSpaceUsed varchar(10),
UnusedSpace varchar(10)
)
goto calculate_tablesize
end

calculate_tablesize:
declare @tablename nvarchar(50)
declare @cmd nvarchar(50)

declare c1 cursor for select name from sysobjects where xtype='u'
open c1
fetch c1 into @tablename
while @@fetch_status = 0
begin
set @cmd='exec sp_spaceused['+@tablename+']'
insert into #SpaceUsed exec sp_executesql @cmd
fetch next from c1 into @tablename
end

select * from #SpaceUsed

drop table #SpaceUsed
deallocate c1

View 20 Replies View Related

Database Tables And User Names

Jan 26, 2007

We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.

For example, to query a table called Page we would nee to do this on the new box.

SELECT *
FROM webdev.page

unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?

Thank you for your help...sorry for the newbie type question.

View 3 Replies View Related

Can't Expand User Database Tables

Dec 5, 2007

We have a person who CAN connect to a named instance in SQL Server Management Studio. There is nothing in the log for a failed login for him. However, when he tries to expand the Tables folder under the only user database in the instance, he gets the error: Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum). Additional Information: An exception occured while executing a Transact-SQL statement or batch. Select permission denied on object 'extended_properties',database 'mssqlsystemresource', schema 'sys'(Microsoft SQL Server, Error:229).

He is trying to expand the Tables folder under a database named ADSALLDB. He can see the folder as well as the other folders (i.e. Views, Synonyms, Programmability,etc.), but can't expand any of them. He can expand the folders under the system databases.

Other uses set up just like him can connect. He is set up with read/write access to the database ADSALLDB. He can expand the system databases.

This is a named instance in a 6 node cluster. SQL Server 2005 SP1 Build Level 2221.

Thank you for any help.

View 2 Replies View Related

Prevent User From Deleting Tables

Dec 12, 2007

Hi,

How can I prevent a colleage to delete tables in a specific database.
Yes he has access to Enterprise Manager. We would like to allow him read only to the live databases.

Is this possible?

View 4 Replies View Related

Finding Out All The User Tables Being Used By An Application

Jul 23, 2005

Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.

View 3 Replies View Related

Allowing A User Access To Only A Few Tables

Jul 20, 2005

With MS SQL 2000 Enterprise Manager, is there a way to allow a user accessto only a few tables, but deny the user access to the rest without having togo to all of the tables and denying access? The database has roughly 50tables, but only 3 should be granted to the new user, so as you can see itwould be a painstaking task to manually do this with the *cough* mouse. Or,if I can run some sort of grant script, that would work too. Thank you!

View 2 Replies View Related

Query To Get All User Tables With Columns

Jul 20, 2005

Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!

View 1 Replies View Related

Dropping Statistics From User Tables

Apr 21, 2008

Hi,

I have founde such a nice code to create statements for dropping all statistics in a database.


DECLARE @tblname sysname, @statname sysname, @sql nvarchar(2000)

DECLARE c CURSOR FOR

SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1

OPEN c

FETCH NEXT FROM c INTO @tblname, @statname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'DROP STATISTICS [' + @tblname + '].[' + @statname + ']'

PRINT @sql

FETCH NEXT FROM c INTO @tblname, @statname

END

CLOSE c

DEALLOCATE c



Please help me with changing this code to take care only on indexes from my own tables (no system ones).

Thanks for your help.

Przemo

View 5 Replies View Related

User That Can Alter And Modify Tables

Feb 7, 2008

What do I have to do in order to create a user that can alter and modify some/all tables?

Thank you.

View 2 Replies View Related

How Can I Enter A User Input Into 2 Tables Simultanously?

Oct 25, 2004

I'm running asp.net on an XP machine with MSDE 2000 as the database. I want to create a user table with a userid and password. I have a second table that contains details about the user such as home address and phone number etc. When the user first signs up, I want their userid to go into the user table and the user detail table. In my SQL insert command, I can't get @userId to go into both tables. So my question is how do I get the userID to go into both tables or is there a better way of doing this altogether?

Thanks,
Tom

View 1 Replies View Related

How Can I Use The GetSchema To Get A List Of Only User Tables And Views.

Apr 26, 2006

How can I use the GetSchema method new in 2.0 to get a list of only user tables and views.

View 6 Replies View Related

Retrieving Rights Of An User On All The Tables In A Database

Aug 8, 2001

Hi,
How to ennumerate the Rights of an User for all the tables [Select/Insert/Update/Delete] in a database or how to ennumerate/list all the Table rights for a particular user in a database? By User, i mean the Login names [like bill, sam, sa] and not dbowner, public, etc. thanx in advance.

View 2 Replies View Related

Dropping User Created Tables From Master Db

Aug 25, 2006

Hi,
I have created a bunch of tables in the Master db by mistake.I want to drop those tables.Please tell me away to drop those.

Thanks!!

View 2 Replies View Related

Temporary Tables In Multi User Environment

Feb 6, 2004

Hi,

I am developing reporting application (access project) which will be used in multi user environment.

Here is what I have:

1 SQLServer database for many users

Each report will be based on:
stored procedure which creates a table filtered for specific dates predefined views will use the newly generated table to show results to the client. However, if more than 1 person runs reports results will not be accurate if each person specified different dates because they will look at the same table and results will match only for a user who called the stored procedure last.

what can you recommend - how to report in multi user environment?


Many thanks

View 2 Replies View Related

User-defined Function And Temporary Tables

Mar 10, 2006

Hiya,Is there really no way to use temporary tables from within a UDF? Ilike UDFs because I can use them like a table -- e.g., SELECT row1,row2 from MyFunc('abc')

View 1 Replies View Related

Problem With User Permissions On Tables - Not Yet Resolved

Jul 20, 2005

I posted a message regarding a problem I'm having with SQL server 7 nottaking any notice of the permissions that I'm setting up on my databasetables (see thread "SQL Server 7 ignores user permissions" started on10/10/2003). I did get one response to this original message, butunfortunately this did not resolve my problem. Can anyone else shed anylight on this issue for me please?Thanks,Jon Ley.

View 1 Replies View Related

Return All User Tables In A Database Where Table Name Is

May 5, 2008



How do I return a list of all user tables in a database where table name starts with EmpID_

for example the table names are:

EmpID_Countries
EmpID_Regions
EmpID_States
EmpID_Colorado
EmpID_Arizona
etc etc...........

I am using SQL Server 2005. Thanks.

View 1 Replies View Related

What Is The User Of Inserted And Deleted Tables In Sql Server 2005

Mar 26, 2008

can anybody tell me with an example how to use Inserted and Deleted in Sql Server 2005

View 7 Replies View Related

SQL Server 2012 :: Find Names Of Tables For A Particular User

Feb 18, 2015

how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.

View 1 Replies View Related

SQL Server 2008 :: Move Tables And SP From Master To User DB

Apr 28, 2015

I just notice that my MASTER database has some user tables and user SP ..and I am thinking to move them to 1 new user database but I am worried it will break something ..

What should I do ?

Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it?

View 5 Replies View Related

Restore Puts Copy Of User Tables In Master

Jul 20, 2005

Environment is SQL Server 2000 64 bit.I restore from a script 'my' database, this works fine. However, allthe tables are also found in master, no data though.Anyone experienced this?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved