SQL Server2016常用基础命令

SQL Server2016常用基础命令（持续更新。。。）

---

---

1. 查询指定表中所有字段名

select name from syscolumns where id = object_id(‘表名’)

2. 查询每张表中所有字段名：

select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name=‘表名称’ and TABLE_CATALOG=‘数据库名称’;

3. 查询指定模式、表的ID

查询指定模式对应id：SELECT SCHEMA_ID sch_name;
查询指定表的id：SELECT OBJECT_ID(N’datebase_name.sch_name.tab_name’) ;

4. 查看端口号

使用下面的SQL Command就可以查看SQL SERVER的端口号:

5. 查询数据库中所有表名称

select table_name from information_schema.tables where TABLE_CATALOG=‘数据库名称’;（包含视图）

select table_name from information_schema.tables where TABLE_CATALOG=‘数据库名称’ and table_type = ‘BASE TABLE’

6. 查询指定表的主键

SELECT COLUMN_NAME AS PK_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ‘.’ + QUOTENAME(CONSTRAINT_NAME)), ‘IsPrimaryKey’) = 1
AND TABLE_NAME = ‘tab_name’ AND TABLE_SCHEMA = ‘sch_name’

7. 查询指定值的行

SELECT * FROM dbo.TEST WHERE C1=1;
注意：除money与small money外，此处=可以用like替换。money与small money使用like查询将查不出结果。

7. 使用select语句查询到的数据建表

oracle: create table table_new as select * from table_old；
sql sever：select * into table_new from table_old;
注意：table_new为新建的表，需在当前模式下不存在，该方式创建的table_new表结构与原表相同，即拥有相同的列类型与精度（如：char(10)），但不会同步创建主键（即table_old中的主键不再为table_new的主键）。

若仅想建一个表结构想同的新表，但不需要旧表中的数据，可使用如下语句：

sql sever：select * into table_new from table_old where 1=0;

8. rowid(%%physloc%%)

• SQL Server的物理地址为%%physloc%%，当使用odbc进行数据库连接时，%%physloc%%不能作为输入参数进行绑定（SQLBindParameter）；可以作为输出参数进行绑定（SQLBindCol）。
• 当插入一条数据时，无法直接返回该行数据的%%physloc%%。

9. 获取SQL Server数据行的物理地址信息

select *, %%physloc%% as rowid from dbTest;

10. 占位符

占位符通常表示为问好(?)或冒号加数字(:1, :2等)。
注意：我在使用ODBC连接SQL Server数据库进行参数绑定时，占位符只能用？而使用冒号加数字会报错。

11. TEXT类型使用ODBC操作的注意事项

• 流程为：SQLPrepare->SQLExcute->SQLFetch->SQLGetData
  注意：不能进行结果集绑定（SQLBindCol），否则会报错：07009 描述符索引无效
• 使用odbc操作text或image（大字段），读取数据时的select语句中，大字段列需排在最后，否则无法获取数据。
  例如(正确)：select col_1, col_2, col_lob from TABLE_1；
  例如(错误)：select col_1, col_lob, col_2 from TABLE_1;
• 时间类型：
• 占位符：（？）或（：数字）。但使用SQLBindParameter时只能用？进行绑定，否则会失败。

12. SQL Server使用output

参考链接
MSDN

13. 为已存在的表添加列COL1,COL2

ALTER TABLE sch_name.tab_name ADD COL1 INT, COL2 VARCHAR(20);

14. SQL Server修改表的非空属性

alter table tab_name alter column col_name char(10) null;
注意：

1. SQLServer修改列的非空属性时，不仅要指定待修改的列名，还要指定列的精度，如 C1 char(10)，若不指定精度则会使用默认精度，导致与原表结构可能不符。
2. SQLServer无法批量修改列的非空属性，只能一列一列修改。

15. SQL Server可以查询的一些系统属性值

当想获取表中某个属性值（如该列是否允许为空：IS_NULLABLE），但不知道该属性在SQLServer数据库中对应的变量名时，可以使用下述语句查看相关属性名称：

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=‘dbo’ and table_name=‘test’;（我用的这个）
SELECT * FROM sys.syscolumns;
SELECT * FROM sys.all_columns;

16. SQL Server全类型建表语句

USE database_name
BEGIN TRANSACTION
CREATE TABLE sch_name.tab_name(C1 BIGINT);
INSERT INTO sch_name.tab_name(C1) VALUES(1111);
COMMIT;

类型名与插入值对应如下：

类型	值
BIGINT	1111
BINARY(50)	0X1189
BIT	1
CHAR(10)	‘A’
DATE	‘2023-01-01’
DATETIME	‘1905-06-23 00:00:00.000’
DATETIME2	‘2020-06-23 09:36:37’
DATETIMEOFFSET	‘2007-05-06 12:35:27.1234567 +12:15’
DECIMAL(18, 0)	12.3
FLOAT	12.3
GEOGRAPHY	geography :: STGeomFromText('POINT(55.9271035250276 -3.29431266523898))
GEOMETRY	geometry :: STGeomFromText(‘POLYGON((-122.358 47.635, -122.348 47.649, -122.348, 47.648, -122.358 47.658, -122.358 47.635))’)
HIERARCHYID	‘/1/1/1’
IMAGE	0XFFFFFFFF
INT	1234
MONEY	1234
NCHAR(10)	‘ABCD’
NTEXT	‘ABCD’
NUMERIC(18, 0)	123.45
NVARCHAR(50)	‘ABCD’
SMALLDATETIME	‘1900-01-01’
SMALLINT	1
SMALLMONEY	1
SQL_VARIANT	N’Rand’
TEXT	‘ABCD’
TIME(7)	‘2007-02-09 12:23:24’
TIMESTAMP	DEFAULT
TINYINT	1
UNIQUEIDENTIFIER	NEWID()
VARBINARY(50)	0x11
VARCHAR(50)	‘ABCD’
XML	‘<Person><ID>1</ID><NAME>“刘备”</NAME></Person>’