I have to run a ALTER statement against a table, but I don't want to run it needlessly. If my field already has the length required, I don't even want to run that ALTER statement.
Can some T-SQL expert let me know if this is the correct way to find the length of an NVARCHAR field named [Data] in table [Access]?
TIA
DECLARE @dataLen int
SELECT
@dataLen = syscolumns.prec
FROM
syscolumns
LEFT JOIN
sysobjects ON sysobjects.id = syscolumns.id
WHERE
sysobjects.name = 'Access' AND
syscolumns.name = 'Data'
PRINT @dataLen
PS.
I noticed a "length" field in syscolumns, but it's set to be 2x the "prec" field. I figure this is because of the NVARCHAR data type. Unicode char = 2 bytes. Am I correct in assuming this?
-
Minh
WOOH! WOOH!
-
ZippyV
Fired Up
I use:
SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table' AND COLUMN_NAME = 'column'
-
Matthew van Eerde
AKA Maurits
Note that querying the INFORMATION_SCHEMA views is supported (that is, will likely work after a SQL Server upgrade) but querying the system tables directly is not.
-
Dr Herbie
Horses for courses
I love INFORMATION_SCHEMA!Matthew van Eerde said:Note that querying the INFORMATION_SCHEMA views is supported (that is, will likely work after a SQL Server upgrade) but querying the system tables directly is not.
Here's a quick crib-list of INFORMATION_SCHEMA that I find handy for reference.
Herbie
-
Minh
WOOH! WOOH!
Thank you so much guys.Dr Herbie said:
I love INFORMATION_SCHEMA!Matthew van Eerde said:*snip*
Here's a quick crib-list of INFORMATION_SCHEMA that I find handy for reference.
Herbie
P.S.
Is there a worse feeling in your stomach than running a ALTER script on a production database?
-
Dr Herbie
Horses for courses
Oh yes - getting a phone call from a customer at 16:50 saying 'where's our data gone?' when you know that one of your developers has been forced into a 'manual data fix' on the live server.Minh said:
Thank you so much guys.Dr Herbie said:*snip*
P.S.
Is there a worse feeling in your stomach than running a ALTER script on a production database?
4 hours later we got it all back again.
Herbie
-
Minh
WOOH! WOOH!
Even though I've tested the script on the DEV server, I'm still trying to run this thing in my head & trying to head off any unforeseen issues... What I wouldn't give for a DB Compare utility!!! No, Red Gate, you cannot haz my $650.Dr Herbie said:
Oh yes - getting a phone call from a customer at 16:50 saying 'where's our data gone?' when you know that one of your developers has been forced into a 'manual data fix' on the live server.Minh said:*snip*
4 hours later we got it all back again.
Herbie
PS
I found these guidelines to be really helpful in creating SQL scripts:
1) Can run script multiple times w/out errors
2) A new script is cumulative of all changes in previous scripts...
-
TommyCarlier
Widen your gaze
If you want to test your script and find out what it's doing without destroying the data, you could run your script in a transaction and just roll back instead of commit. Another option (if you're using SQL Server 2005 or 2008) is to use snapshots. Create a snapshot, execute your script and if something went wrong, restore the database to its state before the snapshot.Minh said:
Even though I've tested the script on the DEV server, I'm still trying to run this thing in my head & trying to head off any unforeseen issues... What I wouldn't give for a DB Compare utility!!! No, Red Gate, you cannot haz my $650.Dr Herbie said:*snip*
PS
I found these guidelines to be really helpful in creating SQL scripts:
1) Can run script multiple times w/out errors
2) A new script is cumulative of all changes in previous scripts... -
Minh
WOOH! WOOH!
That's a good idea, Tommy. I've been basing my code off the scripts Enterprise Manager creates, which contains GOs all over the place. A GO indicates a transaction commit right?TommyCarlier said:
If you want to test your script and find out what it's doing without destroying the data, you could run your script in a transaction and just roll back instead of commit. Another option (if you're using SQL Server 2005 or 2008) is to use snapshots. Create a snapshot, execute your script and if something went wrong, restore the database to its state before the snapshot.Minh said:*snip*
It's too bad that the data layer is horridly lagging behind in terms of tools. I see some positive steps w/ exposing the data object models w/ VS 2005, but man, why is the latest "enterprise manager" sooooooooo slow?
-
Matthew van Eerde
AKA Maurits
> A GO indicates a transaction commit right?Minh said:
That's a good idea, Tommy. I've been basing my code off the scripts Enterprise Manager creates, which contains GOs all over the place. A GO indicates a transaction commit right?TommyCarlier said:*snip*
It's too bad that the data layer is horridly lagging behind in terms of tools. I see some positive steps w/ exposing the data object models w/ VS 2005, but man, why is the latest "enterprise manager" sooooooooo slow?
Not really. Actually, GO is not TRANSACT-SQL at all; it's a meta-command telling the query editor "OK, that's the end of the chunk of SQL to run."
So, for example, if you declare a local variable, it no longer exists after a GO. On the other hand, you can't do two CREATE VIEW statements without a GO in between.
EDIT: the thingies between GOs are called "batches", it would seem. "CREATE VIEW" must be the first statement in a batch.
-
Dr Herbie
Horses for courses
My habit now is that when I open a query window to type:Matthew van Eerde said:
> A GO indicates a transaction commit right?Minh said:*snip*
Not really. Actually, GO is not TRANSACT-SQL at all; it's a meta-command telling the query editor "OK, that's the end of the chunk of SQL to run."
So, for example, if you declare a local variable, it no longer exists after a GO. On the other hand, you can't do two CREATE VIEW statements without a GO in between.
EDIT: the thingies between GOs are called "batches", it would seem. "CREATE VIEW" must be the first statement in a batch.
begin transaction
go
rollback transaction
go
... and then add my SQL in between. When I'm happy, I'll change the rollback to a commit.
Herbie
-
harumscarum
out of memory
Maybe you should somehow default this in your sql app Tommy. I never do this because I never make mistakes with sql but it would be good for the traineesDr Herbie said:
My habit now is that when I open a query window to type:Matthew van Eerde said:*snip*
begin transaction
go
rollback transaction
go
... and then add my SQL in between. When I'm happy, I'll change the rollback to a commit.
Herbie
-
Matthew van Eerde
AKA Maurits
At some point everyone makes the mistake of running a DELETE statement without the WHERE clause.harumscarum said:
Maybe you should somehow default this in your sql app Tommy. I never do this because I never make mistakes with sql but it would be good for the traineesDr Herbie said:*snip*
(Feature request: database option, defaulted to "on", to make DELETE statements without WHERE clauses a syntax error.)
-
vesuvius
Count Orlock
Insert is also just dangerous, but if at all possibe I get a copy of the DB and run the test on that. Intelligent people can look incredibly stupid if not cautious here.Matthew van Eerde said:
At some point everyone makes the mistake of running a DELETE statement without the WHERE clause.harumscarum said:*snip*
(Feature request: database option, defaulted to "on", to make DELETE statements without WHERE clauses a syntax error.)
-
harumscarum
out of memory
No way I never make mistakes! Actually my last fiasco was more with table design. I thought that a key value pair design would work perfect. I am sure you can guess how that turned out.Matthew van Eerde said:
At some point everyone makes the mistake of running a DELETE statement without the WHERE clause.harumscarum said:*snip*
(Feature request: database option, defaulted to "on", to make DELETE statements without WHERE clauses a syntax error.)
I was actually wondering this week if sql server had some flag you could set when a large set of records would be affected by a sql statement.
-
Minh
WOOH! WOOH!
I think this would be cool... A DB change engine...harumscarum said:
No way I never make mistakes! Actually my last fiasco was more with table design. I thought that a key value pair design would work perfect. I am sure you can guess how that turned out.Matthew van Eerde said:*snip*
I was actually wondering this week if sql server had some flag you could set when a large set of records would be affected by a sql statement.
You have declarations of what a db should look like, and run the engine on on a DB, and the engine's job is to make it look like what you've declared.
Should make it a built-in function of SQL server, I mean how hard can it be, right?
-
Next time try... http://msdnforumsucks.com/forums/
Now looking for moderators!
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.