老鬼的博客 来都来啦，那就随便看看吧~
sqlserver常用sql
发布于: 2019-11-12 更新于: 2021-02-02 分类于:  阅读次数: 

-1.友邦查询表字段详情

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
SELECT
	'表名' = CASE
WHEN a.colorder = 1 THEN
	d.name
ELSE
	''
END,
 '表说明' = CASE
WHEN a.colorder = 1 THEN
	isnull(f. VALUE, '')
ELSE
	''
END,
 '字段序号' = a.colorder,
 '字段名' = a.name,
 '中文名' = isnull(g.[value], ''),
 '' AS '业务含义',
 '' AS '业务规则',
 '主键' = 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
	'YES'
ELSE
	'NO'
END,
 '是否允许空' = CASE
WHEN a.isnullable = 1 THEN
	'YES'
ELSE
	'NO'
END,
 '字段类型类型' = b.name,
 '长度' = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
 '精度' = a.length,
 '小数位数' = isnull(
	COLUMNPROPERTY(a.id, a.name, 'Scale'),
	0
),
 '默认值' = isnull(e. TEXT, '')
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
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
WHERE
	d.name = 'attract_database_type' --如果只查询指定表,加上此条件
ORDER BY
	a.id,
	a.colorder;

0.查询整个库或者一个表的字段详情

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
SELECT
	'表名' = CASE
WHEN a.colorder = 1 THEN
	d.name
ELSE
	''
END,
 '表说明' = CASE
WHEN a.colorder = 1 THEN
	isnull(f. VALUE, '')
ELSE
	''
END,
 '字段序号' = 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
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
WHERE
	d.name = 'tookeen_config' --如果只查询指定表,加上此条件
ORDER BY
	a.id,
	a.colorder;

1.sql alter新增删除更新字段

  • 说明
    1
    2
    3
    4
    5
    6
    7
    8
    --新增字段
    	ALTER TABLE <表名> ADD <字段名> <字段类型> NULL;
    	ALTER TABLE tookeen_extras_auth ADD pay_way VARCHAR(20) NULL;
    --删除字段
    	ALTER TABLE <表名> DROP COLUMN <字段名>
    	ALTER TABLE tookeen_elite_auth DROP COLUMN pay_way ;
    --更新字段
    ALTER TABLE <表名> alter COLUMN <字段名> 新类型名(长度)
  • 例子
    1
    2
    3
    ALTER TABLE ivs_wx_user alter COLUMN client_name varchar(200);
    		
    ALTER TABLE ivs_error_log alter COLUMN referer varchar(1000);

2.sql全角半角

  • sql

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    CREATE FUNCTION f_Convert( 
    @str NVARCHAR(4000), --要转换的字符串 
    @flag bit --转换标志,0转换成半角,1转换成全角 
    )RETURNS nvarchar(4000) 
    AS 
    BEGIN 
    DECLARE @pat nvarchar(8),@step int,@i int,@spc int 
    IF @flag=0 
    SELECT @pat=N'%[！-～]%',@step=-65248, 
    @str=REPLACE(@str,N'　 ',N' ') 
    ELSE 
    SELECT @pat=N'%[!-~]%',@step=65248, 
    @str=REPLACE(@str,N' ',N'　 ') 
    SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) 
    WHILE @i> 0 
    SELECT @str=REPLACE(@str, 
    SUBSTRING(@str,@i,1), 
    NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)) 
    ,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) 
    RETURN(@str) 
    END

  • 执行

    1
    update mp_customer set address=dbo.f_Convert(address,0);

3.创建索引

1
2
CREATE  INDEX INDEX_AGENT_CD 
ON mp_customer(agent_cd) ;

4.日期转换函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Select CONVERT(varchar(100), GETDATE(), 0);		//	12 13 2016  2:48PM
Select CONVERT(varchar(100), GETDATE(), 1);		//	12/13/16
Select CONVERT(varchar(100), GETDATE(), 2);		//	16.12.13
Select CONVERT(varchar(100), GETDATE(), 3);		//	13/12/16
Select CONVERT(varchar(100), GETDATE(), 4);		//	13.12.16
Select CONVERT(varchar(100), GETDATE(), 5);		//	13-12-16
Select CONVERT(varchar(100), GETDATE(), 6);		//	13 12 16
Select CONVERT(varchar(100), GETDATE(), 7);		//	12 13, 16
Select CONVERT(varchar(100), GETDATE(), 8);		//	14:50:13
Select CONVERT(varchar(100), GETDATE(), 9);		//	12 13 2016  2:50:13:407PM

Select CONVERT(varchar(100), GETDATE(), 10);	//	12-13-16
Select CONVERT(varchar(100), GETDATE(), 11);	//	16/12/13
Select CONVERT(varchar(100), GETDATE(), 12);	//	161213
Select CONVERT(varchar(100), GETDATE(), 13);	//	13 12 2016 14:51:38:827
Select CONVERT(varchar(100), GETDATE(), 14);	//	14:51:38:857
Select CONVERT(varchar(100), GETDATE(), 20);	//	2016-12-13 14:51:38
Select CONVERT(varchar(100), GETDATE(), 21);	//	2016-12-13 14:51:38.903
Select CONVERT(varchar(100), GETDATE(), 22);	//	12/13/16  2:51:38 PM
Select CONVERT(varchar(100), GETDATE(), 23);	//	2016-12-13
Select CONVERT(varchar(100), GETDATE(), 24);	//	14:51:39
Select CONVERT(varchar(100), GETDATE(), 25);	//	2016-12-13 14:55:34.937

Select CONVERT(varchar(100), GETDATE(), 100);	//	12 13 2016  2:55PM
Select CONVERT(varchar(100), GETDATE(), 101);	//	12/13/2016
Select CONVERT(varchar(100), GETDATE(), 102);	//	2016.12.13
Select CONVERT(varchar(100), GETDATE(), 103);	//	13/12/2016
Select CONVERT(varchar(100), GETDATE(), 104);	//	13.12.2016
Select CONVERT(varchar(100), GETDATE(), 105);	//	13-12-2016
Select CONVERT(varchar(100), GETDATE(), 106);	//	13 12 2016
Select CONVERT(varchar(100), GETDATE(), 107);	//	12 13, 2016
Select CONVERT(varchar(100), GETDATE(), 108);	//	14:56:41
Select CONVERT(varchar(100), GETDATE(), 109);	//	12 13 2016  2:56:41:977PM
Select CONVERT(varchar(100), GETDATE(), 110);	//	12-13-2016
Select CONVERT(varchar(100), GETDATE(), 111);	//	2016/12/13
Select CONVERT(varchar(100), GETDATE(), 112);	//	20161213
Select CONVERT(varchar(100), GETDATE(), 113);	//	13 12 2016 14:56:42:087
Select CONVERT(varchar(100), GETDATE(), 114);	//	14:56:42:117
Select CONVERT(varchar(100), GETDATE(), 120);	//	2016-12-13 14:56:42
Select CONVERT(varchar(100), GETDATE(), 121);	//	2016-12-13 14:56:42.180
Select CONVERT(varchar(100), GETDATE(), 126);	//	2016-12-13T14:56:42.227
Select CONVERT(varchar(100), GETDATE(), 130);	//	14 ???? ????? 1438  2:57:45:717PM

5.查询一个表的所有字段

1
select name from syscolumns where id=object_id( 'mp_customer ')

6.查询一个datasource表中的自定义的表,视图,存储过程

1
2
3
4
5
6
-- 查询自定义的表
select * from sysobjects where xtype='U';
-- 查询自定义的存储过程
select * from sysobjects where xtype='P';
-- 查询自定义的视图
select * from sysobjects where xtype='V';

7.查询一个表的字段详情

1
2
3
4
5
6
7
8
9
10
11
select
	o.name as tbname,
	c.name as columnname,
	t.name as typename,
	c.max_length
from sys.tables as o
	join sys.columns as c
on o.object_id=c.object_id
	join sys.types as t
on c.system_type_id=t.system_type_id
where o.name = 'ivs_wx_user';

8.分页查询

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
	TOP 20 *
FROM
	(
		SELECT
			c.*, ROW_NUMBER () OVER (ORDER BY update_time ASC, id) AS rownum
		FROM
			mp_customer c
		WHERE
			c.status = '1'
	) AS A
WHERE
	rownum > 0

9.查询一个表的详细字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT
		(case when a.colorder=1  then d.name else '' end )表名,
		a.colorder 字段序号,
		a.name 字段名,
		(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
	   (case when (SELECT count(*)
		FROM sysobjects
		WHERE (name in
				(SELECT name
				FROM sysindexes
				WHERE (id = a.id) AND (indid in
						 (SELECT indid
						FROM sysindexkeys
						WHERE (id = a.id) AND (colid in
								  (SELECT colid
								 FROM syscolumns
								 WHERE (id = a.id) AND (name = a.name))))))) AND
			(xtype = 'PK'))>0 then '√' else '' end) 主键,
	   b.name 类型,
	   a.length 占用字节数,
	  COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
	  isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
	  (case when a.isnullable=1 then '√'else '' end) 允许空,
	  isnull(e.text,'') 默认值,
	  isnull(g.[value],'') AS 字段说明
FROM  syscolumns  a left join systypes b
on  a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties' and (d.name = 'mp_login' or d.name = 'mp_agent_info')
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.major_id
order by a.id,a.colorder

10.查询view的创建语句

方式1

1
sp_helptext 'tookeen_view_splus_campaign_customer_pool';

方式2

1
2
3
4
5
6
7
SELECT
	TEXT
FROM
	syscomments s1
JOIN sysobjects s2 ON s1.id = s2.id
WHERE
	name = 'tookeen_view_splus_campaign_customer_pool';

11.查询储存过程的创建语句

1
2
3
4
5
6
7
8
SELECT
	TEXT
FROM
	syscomments
WHERE
	id = object_id(
		'update_single_team_policy_num'
	);

12.查询每个库的日志大小和使用率

1
DBCC SQLPERF(LOGSPACE);

13.查询一个库下所有表的条数，大小M

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SELECT
	t.NAME AS TableName,
	s.Name AS SchemaName,
	p. ROWS AS RowCounts,
	SUM (a.total_pages) * 8 AS TotalSpaceKB,
	CAST (
		ROUND(
			(
				(SUM(a.total_pages) * 8) / 1024.00
			),
			2
		) AS NUMERIC (36, 2)
	) AS TotalSpaceMB,
	SUM (a.used_pages) * 8 AS UsedSpaceKB,
	CAST (
		ROUND(
			(
				(SUM(a.used_pages) * 8) / 1024.00
			),
			2
		) AS NUMERIC (36, 2)
	) AS UsedSpaceMB,
	(
		SUM (a.total_pages) - SUM (a.used_pages)
	) * 8 AS UnusedSpaceKB,
	CAST (
		ROUND(
			(
				(
					SUM (a.total_pages) - SUM (a.used_pages)
				) * 8
			) / 1024.00,
			2
		) AS NUMERIC (36, 2)
	) AS UnusedSpaceMB
FROM
	sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
	t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
	t.Name,
	s.Name,
	p. ROWS
ORDER BY
	UsedSpaceMB DESC
*************感谢您的阅读*************