How to detect changes to the structure of databases, tables and even SP

HI,

Any help here is appreciated, I work in a large software company that has many small teams. I am faced with the issue of some of the other teams are changing the structure of tables and views and even SPs and functions without letting the rest know of these changes.

My question is, is there a way of tracking these changes through a job to alert everyone else in case this situation happens.

regards

You could write some TSQL code that will monitor or snapshot system table information, compare it to the database and find the differences. You can also go the 3rd party route and look at tools like Redgate's SQLCompare to do this for you. Your best bet however is to use your source code control system to track the changes and maintain them (I am assuming you already have such system in place). Apart from these, there are no built-in methods that are easy enough to detect differences out of the box.|||

That is what i thought, but I was hopping for an easy solution. Currently we are tracking it with source control although not all developers adhere to the rules.

thanks anyways for your quick response.

emad

|||

Yeah, RedGate has a nice snapshot feature that you can use to keep a version around of a server. I use SQLCompare when I am migrating changes just to make sure nothing has changed that hasn't been done quite right.

In 2005, you might also consider using a DDL trigger to capture changes (I do this to make sure no one makes any unkown changes. You could then check this table for who has made changes (of course if the changer has dbo rights they could disable this trigger before making changes, but if you have malicious programmers (rather than those who ar just too lazy to do things right :) then you have way more problems than any of us can help you with :)

|||I understand that the DML trigger can be used to find a one or many changes that happen to data in a user table purposely or otherwise. I did that using deleted, inserted, and updated tables. I do not know how to get the user name or id that changed it. Where can I get this information from?. Also, is it possible to get the column names in these three tables? Please advise. Thanks.|||DML trigger is for tracking data changes. You can use SYSTEM_USER to track login information for example. But the relevance of this information depends on your application architecture and how the interaction with the database happens from the end-user. DDL triggers (new in SQL Server 2005) on the other hand can be used to track schema changes for example and that is more closer to what you want.sql

how to delete all the data in the sql server?

Hi,
Is there a way that i can delete all the data from
all the tables in the sql server, i want to remain the
table's structure and user define function and i dun want
to delete the whole database.
Thank you.
regards,
florenceNo command per se. You'd have to execute a DELETE or TRUNCATE TABLE
statement against all tables. Such is easy to generate or automate (using a
cursor), however you have to consider foreign keys so you do it in the right
order. Or you can run the statements several times.
I suggest that you generate script for your DDL. You really should have your
DDL in a script file (or one file per object) anyhow, so you can generate
script, tidy up and comment the script. You can then drop the database and
re-create it from that script file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Hi Florence.
You might find it easier to simply script the database schema (including
stored procs, functions etc), using the SQL Enterprise Manager's scripting
tools, then dropping the database & creating an empty one from the script.
The bigger the database, the more effective this approach will be.
Regards,
Greg Linwood
SQL Server MVP
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Hi,
Execute this command in SQL Analyzer, Replace dbname with your database
name. This commands will delete all the data from all the user tables.
Careful !!! Truncate is not a logged operation, After issuing this command
you will not be able rollback the data.
use dbname
go
declare @.x nvarchar(255)
set @.x=''
select @.x = @.x+' truncate table ' + name
from sysobjects where type='U'
exec sp_executesql @.x
go
Thanks
Hari
MCDBA
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Truncate table won't work for tables that are referred to by foreign keys.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
Truncate table is logged, and can be rolled back:
use tempdb
go
create table t(c1 int identity)
insert t default values
insert t default values
insert t default values
go
select * from t
begin tran
truncate table t
select * from t
rollback
select * from t
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute this command in SQL Analyzer, Replace dbname with your database
> name. This commands will delete all the data from all the user tables.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
>
> use dbname
> go
> declare @.x nvarchar(255)
> set @.x=''
> select @.x = @.x+' truncate table ' + name
> from sysobjects where type='U'
> exec sp_executesql @.x
> go
> Thanks
> Hari
> MCDBA
>
> "florence" <florencelee@.visualsolutions.co.my> wrote in message
> news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > Hi,
> >
> > Is there a way that i can delete all the data from
> > all the tables in the sql server, i want to remain the
> > table's structure and user define function and i dun want
> > to delete the whole database.
> >
> > Thank you.
> >
> > regards,
> >
> > florence
>|||You may want to generate a script to first drop all the foreign keys and
generate another script to then add the foreign keys back. Run the first
script before the TRUNCATE TABLE statements, and then run the second script
afterwards.
Also, I'd probably re-write the script as follows (with some minor changes):
use dbname
go
declare @.x varchar(8000)
set @.x=''
select @.x = @.x+' truncate table ' + QUOTENAME(name) + char(13) + char(10)
from sysobjects where type='U'
exec sp_executesql @.x
go
Note that varchar(8000) still may not be enough.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute this command in SQL Analyzer, Replace dbname with your database
> name. This commands will delete all the data from all the user tables.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
>
> use dbname
> go
> declare @.x nvarchar(255)
> set @.x=''
> select @.x = @.x+' truncate table ' + name
> from sysobjects where type='U'
> exec sp_executesql @.x
> go
> Thanks
> Hari
> MCDBA
>
> "florence" <florencelee@.visualsolutions.co.my> wrote in message
> news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > Hi,
> >
> > Is there a way that i can delete all the data from
> > all the tables in the sql server, i want to remain the
> > table's structure and user define function and i dun want
> > to delete the whole database.
> >
> > Thank you.
> >
> > regards,
> >
> > florence
>|||Thanks a lot Tiber for the information.
I was under the impression that DDL will automatically commit the
transactions.
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OYvZ3rtpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> Truncate table won't work for tables that are referred to by foreign keys.
>
> > Careful !!! Truncate is not a logged operation, After issuing this
command
> > you will not be able rollback the data.
> Truncate table is logged, and can be rolled back:
> use tempdb
> go
> create table t(c1 int identity)
> insert t default values
> insert t default values
> insert t default values
> go
> select * from t
> begin tran
> truncate table t
> select * from t
> rollback
> select * from t
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Execute this command in SQL Analyzer, Replace dbname with your database
> > name. This commands will delete all the data from all the user tables.
> >
> > Careful !!! Truncate is not a logged operation, After issuing this
command
> > you will not be able rollback the data.
> >
> >
> > use dbname
> > go
> > declare @.x nvarchar(255)
> > set @.x=''
> > select @.x = @.x+' truncate table ' + name
> > from sysobjects where type='U'
> > exec sp_executesql @.x
> > go
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "florence" <florencelee@.visualsolutions.co.my> wrote in message
> > news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > > Hi,
> > >
> > > Is there a way that i can delete all the data from
> > > all the tables in the sql server, i want to remain the
> > > table's structure and user define function and i dun want
> > > to delete the whole database.
> > >
> > > Thank you.
> > >
> > > regards,
> > >
> > > florence
> >
> >
>