SQL中添加表列、字段描述及默认值和删除字段的操作
5星
- 浏览量: 0
- 大小:None
- 文件类型：None
简介：
本教程详细介绍了在SQL中添加新表列及其字段描述与默认值的方法，并讲解了如何安全地删除不再需要的字段。
在SQL语句中执行如下操作：
1. 删除表（如果存在）：
```sql
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[JHCRM_Report_ss]) AND OBJECTPROPERTY(id, NIsUserTable) = 1)
DROP TABLE [JHCRM_Report_ss]
```
2. 创建新表（如果不存在）：
```sql
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[Table1]) AND OBJECTPROPERTY(id, NIsUserTable) = 1)
CREATE TABLE [Table1] ([col1] int,[col2] int)
```
3. 添加新字段：
```sql
IF NOT EXISTS (SELECT * FROM syscolumns WHERE syscolumns.[name]=NFNewObjectEnd AND syscolumns.[id]=(SELECT id FROM dbo.sysobjects WHERE id=OBJECT_ID(N[dbo].[jsc_financial]) AND OBJECTPROPERTY(id, NIsUserTable) = 1))
ALTER TABLE jsc_financial ADD FNewObjectEnd int NULL
-- 添加字段描述
EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N先用项目结项数 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE, @level1name=Njsc_financial, @level2type=NCOLUMN, @level2name=FNewObjectEnd
```
4. 添加新字段：
```sql
IF NOT EXISTS (SELECT * FROM syscolumns WHERE syscolumns.[name]=NRecord_Money AND syscolumns.[id]=(SELECT id FROM dbo.sysobjects WHERE id=OBJECT_ID(N[dbo].[JHCRM_Resource_Record_Main]) AND OBJECTPROPERTY(id, NIsUserTable) = 1))
ALTER TABLE JHCRM_Resource_Record_Main ADD Record_Money numeric(12, 2) NULL
-- 添加字段描述
EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N资源费用 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE, @level1name=NJHCRM_Resource_Record_Main, @level2type=NCOLUMN, @level2name=Record_Money
```
5. 删除字段：
```sql
IF NOT EXISTS (SELECT * FROM syscolumns WHERE syscolumns.[name]=NtableName AND syscolumns.[id]=(SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N[dbo].[tableName]) AND OBJECTPROPERTY(id, NIsUserTable) = 1))
ALTER TABLE tableName DROP COLUMN tableName
```
全部评论 (0)
