English 中文(简体)
第20栏
原标题:Find out if column has varchar 20

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 
最佳回答

< 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
问题回答
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

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

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.

你应该能够利用类似于这一询问的某种东西,以找到你一栏是否是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




相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

难以执行 REGEXP_SUBSTR

I m 查询Oracle 10g。 我有两张表格(样本数据见下文)。 i m 试图提取一些领域

SQL Query Shortcuts

What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2

PHP array callback functions for cleaning output

I have an array of output from a database. I am wondering what the cleanest way to filter the values is example array Array ( [0] => Array ( [title] => title 1 ...

OracleParameter and DBNull.Value

we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work: OracleCommand ...

Running numbers in SQL

I have a SQL-statement like this: SELECT name FROM users WHERE deleted = 0; How can i create a result set with a running number in the first row? So the result would look like this: 1 Name_1 2 ...

How to get SQL queries for each user where env is production

I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module: ...