一、SQL SERVER 附加数据库（可视化操添加数据库失败，可新建查询，执行下列语句）
查看数据库版本：select @@version
EXEC sp_attach_db @dbname = 'MyDB', 【数据库名称】
@filename1 = 'D:\Program\Database\DB_Data.MDF', 【mdf文件路径】
@filename2 = 'D:\Program\Database\DB_log.LDF' 【ldf文件路径】
二、数据库中表及表数据的转移（目标数据库不能存在相同名称的表）
SELECT * INTO [TESTDB].[dbo].tb_sm_customer_info FROM [DB].[dbo].tb_sm_customer_info
三、查询一个表中所有列名，字段的注释
SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
WHERE A.name = '表名'
and B.name like '列名'
四、查询字段存在数据库哪张表中
SELECT * FROM sysobjects s WHERE TYPE='u' -- 查询所有表
AND id IN(SELECT id FROM syscolumns WHERE name='case_id')
五、添加主键
IF NOT EXISTS (SELECT object_name(parent_obj) FROM sysobjects WHERE xtype = 'pk' AND name = 'PK_tb_pay_info')
ALTER TABLE [dbo].[tb_pay_info] ADD CONSTRAINT [PK_tb_pay_info] PRIMARY KEY CLUSTERED ([s_type_id]) ON [PRIMARY]
六、添加列和注释
- 添加列
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=object_id('tb_notice_info') AND name = 'd_reply_wfzslar')
ALTER TABLE tb_fp_notice_info ADD d_reply_wfzslar datetime"); - 给列添加注释
IF NOT EXISTS (SELECT name FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'tb_fp_notice_info', 'column', 'd_reply_wfzslar'))
EXECUTE sp_addextendedproperty N'MS_Description', N'我方答复日', N'user', N'dbo', N'table', N'tb_notice_info', N'column', N'd_reply_wfzslar'");
