SQL Server数据库操作表和字段

一、获取所有库

SELECT dtb.name AS [Database_Name] 
  FROM master.sys.databases AS dtb 
 WHERE (CAST(CASE WHEN dtb.name IN ('master','model','msdb','tempdb') THEN 1 ELSE dtb.is_distributor END AS BIT)=0 
   AND CAST(ISNULL(dtb.source_database_id, 0) AS BIT)=0) 
 ORDER BY [Database_Name] ASC

二、数据库表

注：

type	type_desc
FN	SQL_SCALAR_FUNCTION
IF	SQL_INLINE_TABLE_VALUED_FUNCTION
SQ	SERVICE_QUEUE
U	USER_TABLE
FS	CLR_SCALAR_FUNCTION
D	DEFAULT_CONSTRAINT
PK	PRIMARY_KEY_CONSTRAINT
V	VIEW
S	SYSTEM_TABLE
AF	AGGREGATE_FUNCTION
IT	INTERNAL_TABLE
P	SQL_STORED_PROCEDURE

1、表

--查询
SELECT obj.object_id ID,obj.name TableName,ext.value Description FROM sys.all_objects AS obj
  JOIN sys.extended_properties ext ON obj.object_id=ext.major_id
 WHERE ext.minor_id = 0 AND ext.class = 1 AND ext.name != N'microsoft_database_tools_support' AND obj.type='U'

--创建
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[表名](
	[ID] [int] NOT NULL,
	CONSTRAINT [PK_表名] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--删除
DROP TABLE 表名

--重命名
EXEC sp_rename '旧表名','新表名'

--复制整张表
select * into 新表 from 表名

--复制表结构
select * into 新表 from 表名 where 1=2

--复制表内容
insert into 新表 select * from 表名

 

2、表注释

--添加
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注释内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名'

--修改
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'注释内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名'

--删除
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名'

 

三、数据库表字段

1、表字段

--查询
SELECT a.colorder 字段序号,
	   a.name 字段名称,
	   CASE WHEN COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END 是否为标识,
	   CASE WHEN exists(SELECT 1 FROM sysobjects WHERE xtype='PK' AND name IN(SELECT name FROM sysindexes WHERE indid IN(SELECT indid FROM sysindexkeys WHERE id=a.id AND colid=a.colid))) THEN '√' ELSE '' END 是否为主键,
	   b.name 字段属性,
	   a.length 占用字节,
	   COLUMNPROPERTY(a.id,a.name,'PRECISION') 长度,
	   isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) 小数位数,
	   CASE WHEN a.isnullable=1 THEN '√' ELSE '' END 允许空,
	   isnull(e.text,'') 默认值,
	   isnull(g.[value],'') 说明
  FROM syscolumns a
  LEFT JOIN systypes b ON a.xusertype=b.xusertype
 INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name<>'dtproperties'
  LEFT JOIN syscomments e ON a.cdefault=e.id
  LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid=g.minor_id
 WHERE d.name='表名'
 ORDER BY a.id,a.colorder

--添加（不允许null值加上：NOT NULL，有默认值加上： DEFAULT 默认值  ）
ALTER TABLE 表名 ADD 字段名 数据类型 DEFAULT 默认值 NOT NULL


--添加identity列
alter table 表名 add 字段名 int identity(1,1)

--删除
ALTER TABLE 表名 DROP 字段名

--修改
ALTER TABLE 表名 ALTER COLUMN 字段名 数据类型 DEFAULT 默认值 NOT NULL

--修改identity列：先删除再新增

--重命名
exec sp_rename '表名.字段名','新字段名','column'

2、表字段注释

--查询
SELECT obj.name,ext.value Description,ext.minor_id
  FROM sys.all_objects AS obj
  JOIN sys.extended_properties ext ON obj.object_id=ext.major_id
 WHERE ext.minor_id != 0 AND ext.class = 1 AND obj.type='U' AND obj.name='表名'

--添加
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注释内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'字段名'

--修改
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'注释内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'字段名'

--删除
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'字段名'

四、表约束

--添加
alter table 表名 add constraint 约束名字 DEFAULT 默认值 for 字段名称

--删除
alter table 表名 drop constraint 约束名字