April 25, 2002 at 10:46 am
I have been ,unsuccesfully, trying to write a cursor which get's the maxlength of each column in my table and inserts it into a table.
Reason: We receive quarterly dumps from clients and we need to determine if they are sending us longer strings than they did before. Up to this poitn we just defined really long column lengths so our data loads wouldn't fail.
-Kevin
side question: One of my programmers needs to be able to tell , in her program, if a db is read-only. I pointed her towards sp_helpdb but the results of that proc. are not very program friendly.
-Kevin
April 25, 2002 at 11:46 am
April 25, 2002 at 12:11 pm
Here is what I have so far....
I have 2 versions (a cursor and stand alone sql's).
Note: the second part of this is to compare the last table they sent us to the new one. That is why I need to spool to a table.
Thanks, Kevin.
A cursor that is part way there: (I need to record it to a table)
DECLARE @col nVARCHAR(50)
DECLARE @Statement nVARCHAR(300)
DECLARE @tbl nvarchar(50)
SET @tbl = 'Orders'
DECLARE User_Cursor CURSOR FOR
SELECT syscolumns.name
FROM sysobjects,syscolumns
WHERE sysobjects.id = syscolumns.id
and sysobjects.type = 'u' and dbo.sysobjects.name = @tbl
OPEN User_Cursor
FETCH NEXT FROM User_Cursor INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Statement = N'select max(len( ' + @col + CHAR(13)+ N' ))
as Max_Length from ' + @tbl
---------------------------------------------
2nd try:
CREATE TABLE [dbo].[diff_table2] (
[ident_id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
--[min_length] [int] NULL ,
[max_length] [int] NULL
) ON [PRIMARY]
GO
--step 2
INSERT into diff_table2 (name)select name from syscolumns
where object_id('orders') = id
--step to generate individual sql's
select 'INSERT INTO diff_table2 (max_length) select max(len(' + name + ')) from ' + 'Orders'
name from syscolumns
where object_id('orders') = id
April 25, 2002 at 12:36 pm
I'm slightly confused as to why you need to know if the strings are longer, but that's besides the point.
If you are worried about imports, I'd make an import table with varchar(8000) columns and run the imports. Then you can compare the max lengths.
If you want to know the largest data size, that is different. The column lengths are fixed, for varchar, the max is fixed. Is that what you are looking for?
Steve Jones
April 25, 2002 at 12:57 pm
quote:
side question: One of my programmers needs to be able to tell , in her program, if a db is read-only. I pointed her towards sp_helpdb but the results of that proc. are not very program friendly.
Tell her to use the output from sp_dboption
Ex.
sp_dboption 'pubs', 'read only'
will return in the 2nd column "CurrentSetting" a ON if is, or an off if not. You can also use this to change the state.
also note from BOL
quote:
Execute permissions to display the list of possible database options, the list of options currently set in a database, and the current value of an option in a database (using sp_dboption with 0, 1, or 2 parameters) default to all users.Execute permissions to change an option (using sp_dboption with all parameters) default to members of the sysadmin and dbcreator fixed server roles and the db_owner fixed database role. These permissions are not transferable.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply