Question

Goal:
Find out if column Firstname has varchar(20). If true, alter the table etc.

Problem:
I have problem to find out if column Firstname has varchar 20 inside of (if Exists) Everything take place in SQL server 2008 R2

table Staff
Column:

Firstname varchar(20)  
Lastname varchar(100)
if Exists()   // Find out if column Firstname has varchar(20)
begin 

   // Alter table and its specific column

end

Answer 1

< COLUMNS information schema view is better for this.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = [table] AND COLUMN_NAME = [column]
AND DATA_TYPE =  varchar  AND CHARACTER_MAXIMUM_LENGTH = 20

Answer 2

IF EXISTS
    (SELECT *
    FROM    sys.columns c
    WHERE   c.object_id = OBJECT_ID( MySchema.MyTable )
    AND c.name =  MyColumn 
    AND c.system_type_id = 167 --167 = varchar SELECT * FROM sys.types WHERE name =  varchar 
    AND c.max_length = 20)
BEGIN
    ALTER TABLE MySchema.MyTable
    ALTER COLUMN MyColumn VARCHAR(25) NULL; --or NOT NULL and/or DEFAULT ...
END

Answer 3

如果使用玩具,则要记住最大一栏实际上是大字体的最大尺寸,而不是一栏的实际申报规模。

See here for more info: sys.columns onMSDN

对char/var(面积为n<>>>m>,但对于 n和 n而言,沥青的储存量不同,n<>m>是长体。

nchar is 2 n bytes, so the You will need to do max_length/2 to take the actual declaration amount (1-4000)

nvarchar is also 2 n bytes, 因此,为了达到实际宣布的规模,你需要填写(max_length/2)。虽然Nvarchar还每栏使用另外两条tes,但并未列入ys栏。

www.un.org/Depts/DGACM/index_spanish.htm 还忆及: 任何纳瓦尔果或纳果(最高值)的回报

例代码,选择所有表格和显示类型细节的所有栏目:

SELECT [ + schema_name(t.[schema_id]) + ].[ + t.name + ] AS TableName , [ + c.name + ] AS ColumnName , [ + ty.name + ] AS DataType , max_length_value = CASE WHEN c.max_length > -1 AND (ty.name = nvarchar OR ty.name = nchar ) THEN c.max_length/2 ELSE c.max_length END , max_length_bytes = c.max_length , c.is_nullable FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.object_id = c.object_id INNER JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
nchar and nvarchar fromMSDN :-

nchar[(n)] Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character.

nvarchar[(n|max)] Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

Answer 4

你应该能够利用类似于这一询问的某种东西,以找到你一栏是否是var(20)。这将从信息图表中的观点中挑选,并按照专栏和表格具体标准进行过滤。

select t.name  Table 
    , c.name  Column 
    , c.max_length
from sys.columns c
inner join sys.tables t on
    t.object_id = c.object_id
inner join sys.types y on
    y.system_type_id = c.system_type_id
where c.name =  FirstName 
    and t.name =  MyTable 
    and y.name =  varchar 
    and c.max_length = 20

友情链接