If 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...
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
In the olden days when I used databases that kept each table in a separate file, I used to be able to tell when a specific table was modified by the date stamp on the file.
Is there a method in SQL server to check the "last modifed date" at the table level?
Kazim writes "generally when we find the new software on our customer and when they want to change the software they said "we must see and use the old datas on the new program". but at here the problem is starting for us because of i want to know on any example on the old software which data is stored on which table on sql server or it is possible for this example we can say spy. do you know anything for about this type of the question and how can we check which tables and columnS are affected by the software? thnx."
If your tables contain created and modified/updated dates what is the best practice for these?
1. Should you use UTC dates? 2. Do you use a default for the creation date (I assume yes)? 3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data?
Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.
Any one please tell me is there any possible way to identify the table modified date.
I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates. Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.
I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date can any one help
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Is 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.
I 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
Here'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.
-- 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
Hello,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
I 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?
Does 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......
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?
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?
if 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.
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.
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.
Hi, 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
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.
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?
