Finding Modified Objects
Apr 18, 2005hi,
I want to find the modified database objects(Tables, Proc and Fun) in last 3 days in SQL 2000.
With Regards
Praveen (DBA)
ADVERTISEMENT
Finding Last Updated/modified Date
Oct 2, 2007
Hi,
I am using SQL Server 2000 and have the following questions:
1. How do I know the last updated (data) date using system objects or any other method?
2. How do I know the last modified date of a table using system objects or any other method?
3. How do I know when a table is last accessed
Any help is appreciated
Thanks for your time and help in advance
Kumar
SQL Server 2012 :: Finding Dependencies Of Objects
Dec 27, 2013I need to do some clean up activities in my databases... So i intended to drop unwanted tables and views.
for that I need to find, whether the table being used by any other objects ?
How could I achieve it?
Putting Names Of Objects To Control-flow Loop Creating Objects
Dec 27, 2006please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View RelatedSQL Server 2012 :: Select Data From XML - Objects Within Objects?
Nov 20, 2013passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
Last Modified Row
May 29, 2001Is there any way for me to return the row that was last entered? If so, how do I go about accomplishing this?
For example,
Column1-Plant NameColumn2-Creation DateColumn3-Comments
Bayport 12305/24/01 2:51 AMAirflow became unstable
Bayport 12305/24/01 4:00 AM
Bayport 12305/24/01 5:36 AMNo events
Bayport 12305/24/01 1:00 PM
Bayport 12305/26/01 2:45 PMNo events
Bayport 12305/26/01 3:12 PMStarted liquifier 25% LIN
Bayport 405/24/01 2:51 AMSwung liquifier 0% to LIN
Bayport 405/26/01 5:45 AM
Bayport 405/26/01 5:15 PMLiquifuer @ 25% LIN
Coatesville05/24/01 9:32 AM
Coatesville05/26/01 4:25 PMNo events
If I were to query against 5/26, I would want my result to return as:
Bayport 123 5/26/01 3:12 PM (because this was the last row entered on the 26th) Started liquifier 25% LIN.
Bayport 4 5/26/01 5:15 PM (because this was the last row entered on the 26th) Liquifier @ 35% LIN.
Coatesville 5/26/01 4:25 PM No events.
Modified Date
Apr 16, 2001I am taking a "complete backup" of my production db every day using Backupagent of Arcserver 2000. But the Modified Date of .mdf and .ldf files show an older date. Is it normal?
Thanks
Wilson
SP Last Modified DateTime
Mar 21, 2002Does anyone know how to get stored procedure or for that matter any other object last modified time?
View 1 Replies View RelatedTable Modified
Jan 14, 2004Hi all,
Sql server 7
Is there anyway to findout if list of tables that has been modified after august 2003 in a database.
Waiting for ur reply.
TIA
Adil
Modified Bigtables.sql
Nov 26, 2001Here's an updated version of bigtables.sql that also displays the ratio of index size to data size and the percentage of unused space per table. I've found the index to data ratio particularly helpful for finding and fixing over-indexing.
Cheers
-b
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.11
*
**************************************************************************************/
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
create table #spt_space
(
objidint null,
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
selectid
fromsysobjects
wherextype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB'),
idx_data_ratio = ltrim(str(indexp*100 /data) + '%'),
unused_pct = ltrim(str(unused * 100 /reserved) + '%')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables
Edited by graz to add code tags around the code.
How 2 Find Modified Sps
Jul 26, 2007dear friends,
iam new to ssql server.i want to find the modified date of stored procedures. can any one help me in this
Modified Date
Feb 21, 2008Is there a way to see the last modified date on the Table structure or a stored Procedure in SQL Server 2000?
Thx
How To Know What Schema Was Modified
Jul 20, 2005Hello,Is there a way in MSSQL server to find all the objects in the databasebased on the modified date rather than the created date.Thanks in advanceKumu
View 1 Replies View RelatedTop10 + Modified Records
Dec 18, 2007I have stored procedure to show 10 new record from table:SELECT TOP 10 *FROM tableWHERE (id = @id)ORDER BY id DESC and work fine.I'd like to show 10 new record and include modified record as new record. Wo to do that?
View 5 Replies View RelatedTable's Last Modified Date
Jan 9, 2001Does anyone know how to get a table's 'last modified date' in SQL 7 ?
Sysobjects contains the 'create date', but I can't find a 'last modified date' anywhere......
Getting Number Of Records Modified
Oct 20, 2004Hi all
My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?
please some one guide me how to do that?
Your help is highly appreciated
SELECT Last Modified Data
Sep 24, 2004Hi,
I have data that sometimes will be modify. when modify the new data will be insert and the old data still remain inside the database. The same item have their own unique id. So I want to query the data that last modified. How?
Thanks
IDs For Insert And Modified Fields
Jan 21, 2015if you all add ID's with your Inserts and Modified (Date) fields. Such as 1 would indicate if the record was inserted manually, whereas 2 might indicate it was update or inserted by a web application.
View 2 Replies View RelatedWhat Should Be Modified In This Stored Procedure
Apr 13, 2008Hi,
I want to retrieve the values from store_geofence table where the timeduration exceeds certain interval
the procedure is as below
CREATE
procedure [dbo].[GeofenceByCustomer]
@regno nvarchar(50),
@frmdate nvarchar(50),
@todate nvarchar(50),
@Geofence nvarchar(50),
@interval int,
@userid nvarchar(50)
as
begin
declare @val int
Declare @strSql as nvarchar(3000)
Select @val=count(*) from basestation where superuserid=@userid and
base_station_name=@Geofence
--if(@val=0)
begin
create table #templ(registrationno nvarchar(50),basestation
nvarchar(50),entry_time datetime,exit_time datetime,duration
int)
select @strSql='
insert into #templ(registrationno,basestation,entry_time,exit_time,duration)
select
registrationno,basestation,entry_time,exit_time,datediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>'+@frmdate+' and
exit_time<'+@todate+' and datediff(mi,entry_time,exit_time)>'+@interval+'
and basestation in ('+@Geofence+') order by entry_time,registrationno'
execute (@strSql)
print @strSql
select * from #templ
drop table #templ
end
end
GO
I should specify the duration in int to take affect
datediff(mi,entry_time,exit_time)>'+@interval+'
but i do not know how to rewrite this path
i cannot put as
datediff(mi,entry_time,exit_time)>'+@interval+'
it returns an error
Syntax error converting the nvarchar value '
insert into #templ(registrationno,basestation,entry_time,exit_time,duration)
select
registrationno,basestation,entry_time,exit_time,datediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>01/01/2008 and
exit_time<01/01/2008 and datediff(mi,entry_time,exit_time)>' to a column of data type int.
How should i rewrite it
please help
regards
cmrhema
How To Get Modified Column Name In Tables
Sep 4, 2013If we want to get the changes happened in database we will get through sys.objects or sys.tables .but we are only getting it table name . But how to get by table name and what the column added to that table or what column has been modified (datatype or constraint) .
For example if i added employee table column called deptid it should be shown in changes in geiven date
SELECT
modify_date
,type_desc
,name
FROM sys.objects
WHERE is_ms_shipped=0
--AND modify_date>='yyyy/mm/dd' <--optionally put in your date here
ORDER BY 1 DESC
How to get column name of a table which is modified...
Record Datetime Of Last Modified
Apr 16, 2007I have a datetime field in a table want to use to record the date time last the row/field of the row is modified.
Can I do this in SQL Server 2005 without writing any code/trigger?
Thanks
J.
Get File Modified Date
Jan 10, 2008Hi,
the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database.
Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error.
Any thoughts how to do this please?
Thanks
How To Get Modified Time Of A Table
Jul 11, 2006Hello All,I would like to get last modified timestamp for a table. Is there any way toget that information using any commands?Thanks in Advance,Muthu.
View 2 Replies View RelatedLast Modified Date Of A View
Jul 20, 2005How to get the date a view was last modified? (As opposed to created)
View 2 Replies View RelatedGet Modified Date Of File
Jan 10, 2008Hi,
the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database.
Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error.
Any thoughts how to do this please?
Thanks
File And Date Modified
Jul 20, 2007Hello all,
I was just thinking about a situation which I have been tasked with in that I have a package that is scheduled to run once a day which will import data from a flat file. The data does not have any distinguishing time that can be used to see whether or not it is beyond a last imported time.
I was wondering, is there a way to access the date modified of the file? If so I could simply append this time to a table which keeps track of all of the files and the modified dates which I have ever imported for this task.
Last Modified To Store Procedure
Feb 27, 2006Hello everyone,
I have store procedure that was created on 9/15/2005. It went through some modificiations since then. How can I find out when was the last updated or last two update on this object?
Thanks
Last Modified Date Of The Table
Dec 27, 2007Hi,
How to find out the Last Modified (Structure) in the SQL Table?. Is there any query please let me know.
Regards,
S.Balavenkatesh
Datestamping A Modified Record...
Feb 22, 2008
I have a simple SQL2005 database with a single table that I need to be able stamp a record in a "modifiedon" field to represent when it was modified. It's set to a datetime format, but am unsure how to proceed next.
Any help would be appreciated.
- Pete
List Of Modified Table ?
Jan 7, 2008Hi Everybody,
i am it sutck when i am writing this sql in sqlserver2005
my requirement is to list all the table name and along with their filed name if some one modified (table definition ) for a specifc date range ?
is there any way to get these information in sql server 2005 ?
regards
sujithf
SQL Script To Find When Table Last Modified
Nov 8, 2007I am looking for two sql scripts,1. How can i check when a tables structure was last modified? 2. How can i find the structure differences between two databases that were created the same but changed over time individualy?Looking for sql scripts to do this. Any help on either would be great.Thanks
View 2 Replies View RelatedRestoring Rows Accidentaly Modified
Oct 19, 2005Hello!How can I Restore rows which are accidentaly modified.I forgetted to put where condition in "Update" Query.Now I want my data back. I have not taken back up manually yet.I am using SQL Server 2000.Please help.Thank you,
View 3 Replies View RelatedHow To Get DateBase Table Last Modified Date ?
Dec 26, 2005Hi
View 9 Replies View Related