Prev: Help! Cannot delete an orphaned merge push subscription
Next: Moving SSRS Server not the database
From: Charles on 8 Feb 2010 10:25
When creating a sproc in SSMS, a simple code header is automatically
inserted that I can use to save change history, for example. When creating a
view this does not happen as far as I can see, although I can manually enter
one.

When creating a table there is no header and it seems no possibility to
create one; if I create the table from script comments are not retained.

Firstly, I am wondering why the inconsistency, and secondly how can I save
comments where developers enter change history when something in the table
schema is updated? I realise I can use extended properties, but these appear
horribly cumbersome, and not really fit for this purpose. How does everyone
else do it?

[I'm going to try and second guess here, but is is that I should be
maintaining the script and not the database objects?]

TIA

Charles


From: Tibor Karaszi on 8 Feb 2010 11:12
I prefer to have script files as the truth. This allow for comments in these
script files. The short technical answer to your question is that a table is
just a definition (no "code"), so there's no place for a comment to go
anywhere in the CREATE TABLE command.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Charles" <blank(a)nowhere.com> wrote in message
news:uUx56LNqKHA.556(a)TK2MSFTNGP02.phx.gbl...
> When creating a sproc in SSMS, a simple code header is automatically
> inserted that I can use to save change history, for example. When creating
> a view this does not happen as far as I can see, although I can manually
> enter one.
>
> When creating a table there is no header and it seems no possibility to
> create one; if I create the table from script comments are not retained.
>
> Firstly, I am wondering why the inconsistency, and secondly how can I save
> comments where developers enter change history when something in the table
> schema is updated? I realise I can use extended properties, but these
> appear horribly cumbersome, and not really fit for this purpose. How does
> everyone else do it?
>
> [I'm going to try and second guess here, but is is that I should be
> maintaining the script and not the database objects?]
>
> TIA
>
> Charles
>
>
From: Tom Cooper on 8 Feb 2010 11:19
Yes, you should be maintaining the script and not the objects in the
database.

The "best practices" way to do this is to maintain the scripts in an
external source control system. This is not only true for tables,
constraints, etc, you should also do this for stored procedures, functions
etc.

That way, you treat database source code the same way you treat any other
source code - the source control system always has the current running
production source as well as a complete change history.

Tom

"Charles" <blank(a)nowhere.com> wrote in message
news:uUx56LNqKHA.556(a)TK2MSFTNGP02.phx.gbl...
> When creating a sproc in SSMS, a simple code header is automatically
> inserted that I can use to save change history, for example. When creating
> a view this does not happen as far as I can see, although I can manually
> enter one.
>
> When creating a table there is no header and it seems no possibility to
> create one; if I create the table from script comments are not retained.
>
> Firstly, I am wondering why the inconsistency, and secondly how can I save
> comments where developers enter change history when something in the table
> schema is updated? I realise I can use extended properties, but these
> appear horribly cumbersome, and not really fit for this purpose. How does
> everyone else do it?
>
> [I'm going to try and second guess here, but is is that I should be
> maintaining the script and not the database objects?]
>
> TIA
>
> Charles
>
>

From: Hyacinth Pfeiffer on 8 Feb 2010 11:21

Hi Charles,

you may have a look at "fn_listextendedproperty". This mechanism can be
used to store various comments in the database.

Hyacinth


Am 08.02.2010 16:25, schrieb Charles:
> When creating a sproc in SSMS, a simple code header is automatically
> inserted that I can use to save change history, for example. When
> creating a view this does not happen as far as I can see, although I can
> manually enter one.
>
> When creating a table there is no header and it seems no possibility to
> create one; if I create the table from script comments are not retained.
>
> Firstly, I am wondering why the inconsistency, and secondly how can I
> save comments where developers enter change history when something in
> the table schema is updated? I realise I can use extended properties,
> but these appear horribly cumbersome, and not really fit for this
> purpose. How does everyone else do it?
>
> [I'm going to try and second guess here, but is is that I should be
> maintaining the script and not the database objects?]
>
> TIA
>
> Charles
>
>

From: Charles on 8 Feb 2010 12:00
Hi Tibor, thanks for the quick reply.

As I was penning the question I thought this might be the answer. I'm
looking at source control in particular at the moment for the database so I
can see that this is the right way to go.

What I'm struggling with now is how to manage the scripts. I want to use SVN
for source control, and since it doesn't integrate with SSMS I have created
a database project in Visual Studio (2008). I have scripted the database
into one file per object, and I can add these to my database project. But
these are all CREATE scripts, so when I want to modify something, I have to
manually create the ALTER script myself, is that correct?

For example, suppose I want to change the definition of a table column from
VARCHAR(50) to VARCHAR(255). I can't just amend the CREATE TABLE script as I
would have no way of deploying this to an existing database, only to a new
database. So how does that work?

It just strikes me that managing and maintaining change in a database is not
joined up yet. Am I missing something?

Charles


"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:OiAzNlNqKHA.3936(a)TK2MSFTNGP06.phx.gbl...
> I prefer to have script files as the truth. This allow for comments in
> these script files. The short technical answer to your question is that a
> table is just a definition (no "code"), so there's no place for a comment
> to go anywhere in the CREATE TABLE command.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "Charles" <blank(a)nowhere.com> wrote in message
> news:uUx56LNqKHA.556(a)TK2MSFTNGP02.phx.gbl...
>> When creating a sproc in SSMS, a simple code header is automatically
>> inserted that I can use to save change history, for example. When
>> creating a view this does not happen as far as I can see, although I can
>> manually enter one.
>>
>> When creating a table there is no header and it seems no possibility to
>> create one; if I create the table from script comments are not retained.
>>
>> Firstly, I am wondering why the inconsistency, and secondly how can I
>> save comments where developers enter change history when something in the
>> table schema is updated? I realise I can use extended properties, but
>> these appear horribly cumbersome, and not really fit for this purpose.
>> How does everyone else do it?
>>
>> [I'm going to try and second guess here, but is is that I should be
>> maintaining the script and not the database objects?]
>>
>> TIA
>>
>> Charles
>>
>>
 |  Next  |  Last
Pages: 1 2 3
Prev: Help! Cannot delete an orphaned merge push subscription
Next: Moving SSRS Server not the database