新增文章

文章标题分类内容

SELECT name FROM syscolumns  WHERE id = OBJECT_ID('t_CloudPassport')  --【取得 数据表字段】
    
exec p_AlterTableAddColumns @TableName='t_CloudPassport',@Fields='UserId VARCHAR(50)|UserName VARCHAR(50)',@Delimeter='|'  --【如果字段不存在，添加字段】

sp_helptext p_AlterTableAddColumns

/******************************************************************************  
 * FUNCTION NAME: p_AlterTableAddColumns                                      *  
 * CREATED BY:    Michael .H                                                  *  
 * CREATION DATE: 2005-06-15                                                  *  
 * DESCRIPTION:   Generate a dynamic sql to modify table structure            *  
 * PARAMETERS:    @TableName - Table name                                     *  
 *                @Fields - Field list                                        *  
 *                @Delimeter - Delimeter                                      *  
 ******************************************************************************/  
CREATE PROCEDURE p_AlterTableAddColumns  
    @TableName VARCHAR(255),  
    @Fields    VARCHAR(8000),  
    @Delimeter CHAR(1) = ','  
AS  
    SET NOCOUNT ON  
      
    DECLARE @SQL VARCHAR(8000)  
      
    IF NOT EXISTS ( SELECT 1  
                      FROM sysobjects  
                     WHERE id = OBJECT_ID(@TableName)  
                       AND OBJECTPROPERTY(id, 'IsUserTable') = 1)  
        RETURN  
      
    IF EXISTS (SELECT 1  
                 FROM dbo.fn_SplitStringToTable(@Fields, @Delimeter)  
                WHERE SUBSTRING(FValue, 1, CHARINDEX(SPACE(1), FValue)) NOT IN  
                                   (  
                                       SELECT name  
                                         FROM syscolumns  
                                        WHERE id = OBJECT_ID(@TableName)  
                                   )  
              )  
    BEGIN  
        SELECT @SQL = 'ALTER TABLE ' + @TableName  
                                     + SPACE(1)  
                                     + 'ADD'  
                                     + SPACE(1)  
          
        SELECT @SQL = @SQL + FValue + ','  
          FROM dbo.fn_SplitStringToTable(@Fields, @Delimeter)  
         WHERE SUBSTRING(FValue, 1, CHARINDEX(SPACE(1), FValue)) NOT IN (  
                              SELECT name  
                                FROM syscolumns  
                               WHERE ID = OBJECT_ID(@TableName)  
                             )  
      
        SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1)  
        EXECUTE(@SQL)  
    END