Determine Last Access Of A Database Or Table

Does anybody know of a way to determine the last date/time a table has been accessed (query/update)?
I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table.
I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience.
Any info will help greatly!
 
Thanks!
 
 

 



ADVERTISEMENT

How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

 
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
 
1 Has the current user been granted login access to the trusted SQL Server?
 
2 Has the current user been granted access to a specific database?
 
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
 
Thanks,
Sean

View Replies View Related

Query To Determine Public Access

Is there any T-SQL Script or SP that would determine what tables public users have access to?  I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables.  Thanks
-Kyle

View Replies View Related

A Query To Determine A Role Or Id's Access To An Object... Do-able?

Hey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access.  The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!

View Replies View Related

How To Determine Last LSN For Database

 

What is the most reliable way to determine the last LSN of a database?  I've looked in sys.database_files to no avail.  I've also looked in msdb.dbo.backupset which is accurate but only based on backups already performed not the current state of the database.
 
-joe

View Replies View Related

Determine If Autoincrement Is Set In A Table, Form Asp.net

I want to access sqlserver table properties from asp.net .How do i know that a table defined in sqlserver has autoincrement field or not.Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.
I am not being able identify this property from codes.Please help.thank you

View Replies View Related

Determine Table Load Order

Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies?

Thanks,

Fred.

View Replies View Related

Query To Determine Table Size

Hi all,
I am looking for a query to determine the exact size of a large table.

Thanks
JJ

View Replies View Related

Determine Current Table Of Trigger

Good Morning
 
Is There anyway to determine the table you are referencing in a trigger's inserted / deleted tables?
 
i.e would this be possible
print @Table_Name + " was Updated"
 
Where @Table_Name is the table on which the trigger is created
 
(bad example I know but you get the idea)
 
Any input will be appreciated.
 
 
 

View Replies View Related

How To: Determine If A Table Exists Using SQL Or TSQL.

I need a SQL or TSQL command (not a stored procedure) that will determine if a table exists (TBL_PARAMETERS).  The command needs to return a 1 if the table exists or 0 if it dose not exist.
 
Thanks!
Sean
 

View Replies View Related

How To Determine Length Of Table Row In Sql 2005

How, other than by doing the addition on my own, can I detemrine the length of a row in a table?

 

TIA,

 

barkingdog

View Replies View Related

Access A Table From A Different Database

Hi all,
I'm writing a procedure that require to access a table from a different database but from the same server. Can the procedure able to read this table. If so how should my procedure use that table?
Thank you for all your help.

View Replies View Related

How To Access Table In Different Database?

Hi,
I have 2 databases in SQL Server named Database1 and Database2. I have logged in Database1. I would like to see the records of the Table which is present in Database2. Could anyone please tell me How to access the tables in Database2 from Database1?


Thanks,
Sathish kumar D

Sathish kumar D

View Replies View Related

How To Access Table From Different Database

hi,
i am using stored procedure which put data in another table fron different database.

so i am using servername.databasename.dbo.tablename.

if I am change the servername then i have to edit the Stored procedure.
Is there any way to access server name.so that if server name is chane then also i dont have to change stored procedure.

Like in c we use #define where we change value of variable that is reflected in whole program.

View Replies View Related

Need To Determine If Records Are Already In Database

Problem: I can add 4 items to a SQL 7.0 database but I need to redirect the user if all 4 items exist in the same Record elsewhere in the Database.

Clearly I need some kind of statement like "if category1 = var1 AND category2 = var 2 AND..." but don't know where to put it.

Code:

strSQL = "SELECT * FROM tblUserQuarter"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn,adOpenStatic , adLockOptimistic

'Insert choices from report.asp into tblUserQuarter
objRS.AddNew
objRS("cboFY") = Request.Form("cboFY")
objRS("Quarter") = Request.Form("Quarter")
objRS("ID") = Request.Form("ID")
objRS("Report") = Request.Form("Report")
objRS.Update

objRS.Close
Set objRS = Nothing
objConn.Close
set objConn = Nothing

View Replies View Related

Determine Date Database Was Last Used ?

Hi There

I am trying to establish the date a database was last used.

At first i checked out sp_helpfile and sysfiles to see if there was a date last modified, i also tried using xp_cmdshell to check the date on the actual .mdf or .ldf on the o/s but if the file has not grown the date will be the create date.

I need to establish the date a database was last used, either the last time someone logged into it or the last time any sort of command was run against the database, i cannot use sysprocesses as there are no connections to the database, so i need to determine the last time there actually were any processes run against this database.

And i need to use tsql, checking audit logs etc is not viable, can anyone help ?

Thanx

 

View Replies View Related

How Can I Determine The Size Of A Database?

Simple question...

When I use the full version of SQL Management Studio, I can run a report to view the size. How can I do this with SQL Mgmnt Express?

View Replies View Related

How To Determine Table And Column Names In A Trigger?

Hi all,
 
  I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already. 
 
  I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted).  I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.

 
  Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
 
Thanks,
-Mike

View Replies View Related

Transferring ACCESS DataBase(ABC.mdb) To SQL Table Using C#

I want to transfer ACCESS DataBase(ABC.mdb) to SQL Table Using c#.(SSIS Programming)
i need to use this query on access database

which will when run create 4 fileds
ABC.mdb
Sorce_Db_Id_Col

Attribute_Type

Attribute_Value

Query_Flag
 
And sorce database
Indentity_col
Att_typ
Att_value
Att_catg
 
I m very new in this stuff. so please please any one have any sample code for this then send me !
please i realy need it
My email id is ripal.parikh@softwaresolutionsindia.com
Thanks lot!!!!

View Replies View Related

Last Access Time For A Database/Table

 

Hi there is there any way in tsql that I can tell when a database/table was last accessed? We have a lot of *** on our servers that need cleaning up.
 
If it is not possible could I put a trace on the servers that will tell me if any access has occured and run this for a couple of weeks for instance to see if any access has occured?
 
Thanks
 
Daz

View Replies View Related

Need Query To Determine If Database Empty

I would appreciate a query to determine if a database is empty?

Empty meaning that no user tables have any data in them. I expect the system tables in the database
to have data in them.

An alternative idea, but not exactly the same thing, would be the ability to determine it the database
is in its pristine state (creation state, but not yet user modified).

Thanks in advance for your advice/help!!

Gary A.

View Replies View Related

Determine When A New Record Is Added To A Database

I have a SQL database that gets populated by another program. Is there a way to determine when a new record is added to that database?

Thanks

View Replies View Related

How To Determine Sp Caller Current Database?

When executing a stored procedure that is defined in another database, as:
 
USE db1;
 
EXEC db2.dbo.sproc;
 
Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?

View Replies View Related

SP To Determine Layout, Population And Sample Data In Table(s)

Hi! I am new to SQL Server... looking for some veteran assistance.

"Data Integrity Report"

I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).

The cursor/report needs to have the following columns:



Ordinal_Position (I.E. Column number)
Column_Name
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)

Data_Type

Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):

Col Num     Col Name    # Blanks   Difference     Data Type     Col Length    Sample Data
    1             Name            12                 88              varchar              30                Sally Smith
    2             Address         34                 66             varchar               45                123 Main St Apt 45
    3             Acct_ID          0                  100            varchar               4                  AB12345

Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).

Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.

Sorry for the long post, and thanks in advance!
John

View Replies View Related

How To Access The Info Of A Table Created In Other Database

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V

View Replies View Related

How Do I Determine The Backup Date Of A Restored Database..?

Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin

View Replies View Related

Cannot Determine How To Set Owner To Install Database Design

My compatibility settings are fine. Anyone know how to to this?

View Replies View Related

Stored Procedure Can't Access Table On External Database

Hi

I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.

My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').

I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.

Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.


Example of stored procedure on external database:

/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/
CREATE PROCEDURE dbo.AddUser
(
@Username Varchar(20),
@Password Varchar(20)
)
AS
(
Select User_ID From UserList
Where User_Username = @Username
)
GO

Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!

Any help would be greatly appreciated as I am completely stuck.

John

View Replies View Related







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