Get last modified date for sql objects in SQL server 2005 to track database changes.
For frequent migration to different environment we have to provide scripts for any database changes, most of the time developer forget to send few of the scripts which causes error in the other environments.
I am using SQL server 2005.
I was looking for any sql query which can list the create date and modify date of table, stored procedure, views etc in a database.
I google and found the below query but I get the below error message:
" Invalid object name 'sys.objects'."
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
Note: I do not have access to Master database.I have also tired the below query:
SELECT name
FROM sysobjects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7but then I get this error message : Invalid column name 'modify_date'.
Please suggest.
Thanks.
|
0 |
|
gridview 
asp.net.sql-datasource
29906 articles. 
0 followers.
5 Replies
1337 Views
I run the first query in my database and got the results OK. Are you sure your SQL Server uses default non-case sensitive collation?
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
|
|
0 |
|
Naom gridview:
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
Note: I do not have access to Master database.All the "sys" view have been introduced in 2005 only. Whenever a new database gets created, all the system objects ( tables, views, procedures ) get created for that. So, if you access any "sys" object from within any database, that means you are accessing it from that database only ( database specific ). So, you do not necessarily want access to the master database.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7but then I get this error message : Invalid column name 'modify_date'.
The "modify_date" column is a newer entry in the "sys" object specific views. So, you will not find it in the "sysobjects". This way the error is quite obvious.
Can you run the below query and post the results here ?
select @@version
Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."
"Mark as Answer" the post(s) which helped you solve the problem
|
|
0 |
|
dhimant Dhimant,
thanks for your reply, here is the result i get after running @@version:
Microsoft SQL Server 2000 - 8.00.2273 (Intel X86) Mar 7 2008 22:19:58 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
so i am not using SQL server 2005, Is there any equivalent query in SQL Server 2000 which can give 'last modified date' ?
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
Note: I do not have access to Master database.All the "sys" view have been introduced in 2005 only. Whenever a new database gets created, all the system objects ( tables, views, procedures ) get created for that. So, if you access any "sys" object from within any database, that means you are accessing it from that database only ( database specific ). So, you do not necessarily want access to the master database.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7but then I get this error message : Invalid column name 'modify_date'.
The "modify_date" column is a newer entry in the "sys" object specific views. So, you will not find it in the "sysobjects". This way the error is quite obvious.
Can you run the below query and post the results here ?
select @@version
|
|
0 |
|
gridview By quick search I found this http://forums.asp.net/p/948921/1152484.aspx#1152484 so looks like you're out of luck.
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
|
|
0 |
|
Naom thanks for your reply, here is the result i get after running @@version:
Microsoft SQL Server 2000 - 8.00.2273 (Intel X86) Mar 7 2008 22:19:58 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
so i am not using SQL server 2005, Is there any equivalent query in SQL Server 2000 which can give 'last modified date' ?
AFAIK, I'm afraid no, there is no way to find that value. I personally faced similar situation when I was using 2000 and couldn't manage to find the last modified date of crucial objects.
Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."
"Mark as Answer" the post(s) which helped you solve the problem
|
|
0 |
|
dhimant 
Similar Artilces: Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...
Moving SQL Server 2005 Database to SQL Server 2005 Express
Hi, I have SQL Server 2005 in my Development machine but the hosting server has only SQL server 2005 Express. I was wondering if I develop the DB in SQL server 2005 would be able to move/deploy it to SQL Server Express in the production server? If so how? Can I just generate the script and then run it on the productions server? Or there is a better/easier way? Thanks in advanceShuaib----------------------- I don't anticipate any problems creating database in SQL Server 2005 and moving it to Express. If you need to generate the script to create an empty shell (no d...
moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...
Restoring a SQL Server Express 2005 Database to SQL Server Standard 2005
Hello,If I backup and restore an express database to sql server 2005 standard, will there still be limitations in regards to the database size, cpu...etc.? Thanks,Jon I suppose that no. Limitations are connected to SQL engine not to database itself. But probably if your database will grow up connected to SQL Standard and you will try to move it back to SQL express you will be in trouble. ThanksJPazgier...
How can I get the 'entire' database, structure and data, from SQL server 2008 to SQL server 2005?
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either. The only way I know that works is to script the creation of all tables then export and import. This does work. How can I get the 'entire' database, structure and data, from 2008 to 2005? ThanksSQL newbie. Visual Studio 2008 supports a type of project called a database project. You might try importing the schema of t...
Migrate Database from SQL Server 2005 beta 3 to Sql Server 2005 Release
Do you now, How can I move database with datas to release version of sql server? Procedores backup/restore can't help becouse of unsuported version database. Any ideas? What about generate a schema script? Then run an install of your schema in a new query. You could at least get the db schema but not the data.Eric RamseurRainbow Portal 2.0 AdminDownload Rainbow 2.0!!Rainbow Code Rainbow Portal CommunityC# 2005 Group but I have many datas...
SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well. (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed. I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...
Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...
Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!), I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine. But I've had nothing but frustration trying to get it up and running on a webhosting site. I picked hostmysite.com which uses SQL Server 2005. I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database. I tried using SQL Server Management Studio Express. I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...
Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem. Please click "Mark As Answer" if this hepled in solving your problem. ...
Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!), I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine. But I've had nothing but frustration trying to get it up and running on a webhosting site. I picked hostmysite.com which uses SQL Server 2005. I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database. I tried using SQL Server Management Studio Express. I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...
How to convert a SQL Server 2005 Database to SQL Server Express
Hi, I have a SQL Server 2005 Database that I would like to export to SQL Server Express. How do I go about this? I've tried backing it up, creating a new (blank) SQL Server Express Database and trying to restore it, but get the following message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'PRODSOL-LAPTOP1\SQLEXPRESS'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOp...
Restoring a SQL Server 2005 database into SQL Server 2000
Hey guys We currenlty have SQL Server 2000 installed inhouse for testing. A client has given us a backup of their 2005 database and i'm wondering if the restore will work into 2000, or any issues i should be aware of. Should i just install msde locally on my machine and restore the database? thanks Hi,Ask your client to backup database as 2000 format. There is an option for this in Microsoft SQL Server Managment Studio. Then download MSSMSE and install for yourself and try to restore database from it.If database doesn't use any 2005 features (rowcount for exampl...
SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)? Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...
Web resources about - Get last modified date for sql objects in SQL server 2005 to track database changes. - asp.net.sql-datasource
Genetically modified maize - Wikipedia, the free encyclopediaGenetically modified maize ( corn ) is a genetically modified plant. Specific maize strains have been genetically engineered to express agri ...
Performance VW - The World's best loved magazine for the modified Volkswagen sceneGet Performance VW - The World's best loved magazine for the modified Volkswagen scene on the App Store. See screenshots and ratings, and read ...
Flickr is almost certainly the best online photo management and sharing application in the world. Show off your favorite photos and videos to ...
The Surprising Role of Genetically Modified Corn - YouTubeFelicia Wu is an assistant professor of environmental and occupational health at the Graduate School of Public Health as well as an adjunct professor ...
Organic farmer takes genetically modified crop case to WA High Court of AppealAn organic farmer who failed in a bid to sue his neighbour after genetically modified canola blew onto his West Australian property wants to ...
Trio's boat crushed after 69 crabs found under modified floorA modified fishing boat used by three recreational fishers who were convicted of illegal crabbing earlier this year was destroyed on Wednesday ...
Modified online children's privacy rules take effect in US - U.S. Federal Trade Commission, regulation ...Some websites and mobile app developers are confused about how to comply with revised rules governing the online collection of personal information ...
Genetically modified food: The truth about GMO foodIT’S accused of causing cancer, destroying the environment and storing up devastating health risks for our children. Genetically modified ...
Green light for genetically modified apples that don't brownThe US Department of Agriculture says it is okay for two varieties of genetically engineered non-browning apples to be sold in the United States. ...
"If the lab technique works in the field, it could offer a new way of stopping the biting insects from spreading malaria to humans, they say. ...