您所在的位置:首页 > 新闻中心 > 数往知来,Server如何查找表名或列名中包含空格的表和列
公司要闻
数往知来,Server如何查找表名或列名中包含空格的表和列
发布时间:2020-04-02 17:46
访问量:359

 

IF OBJECT_ID('tempdb.dbo.#TabColums') IS NOT NULL DROP TABLE dbo.#TabColums; CREATE TABLE #TabColums( object_id INT , column_id INT) INSERT INTO #TabColumsSELECT object_id , column_idFROM sys.columnsWHERE DATALENGTH(name) != LEN(name) * 2 SELECT TL.name AS TableName, C.Name AS FieldName, T.Name AS DataType, DATALENGTH(C.name) AS COLUMN_DATALENGTH, LEN(C.name) AS COLUMN_LENGTH, CASE WHEN C.Max_Length = -1 THEN 'Max' ELSE CAST(C.Max_Length AS VARCHAR) END AS Max_Length, CASE WHEN C.is_nullable = 0 THEN '×' ELSE N'√' END AS Is_Nullable, C.is_identity, ISNULL(M.text, '') AS DefaultValue, ISNULL(P.value, '') AS FieldComment FROM sys.columns CINNER JOIN sys.types T ON C.system_type_id = T.user_type_idLEFT JOIN dbo.syscomments M ON M.id = C.default_object_idLEFT JOIN sys.extended_properties P ON P.major_id = C.object_id AND C.column_id = P.minor_id INNER JOIN sys.tables TL ON TL.object_id = C.object_idINNER JOIN #TabColums TC ON C.object_id = TC.object_id AND c.column_id = TC.column_idORDER BY C.Column_Id ASC

sqlserver学习_01

启动数据库
开始-》cmd-》进入控制台
   sqlcmd->-S .\sqlexpress
   1>
如果出现表示数据库“sqlexpress实例”可以使用了
   Exit表示退出
安装SQL Server数据库(数据管理软件)
安装服务
   -》一个电脑硬件上可以安装多个实例,一个实例就好比另外一个人
   -》实例 只可以有一个默认实例
1、记录学生的信息
姓名 年龄 性别
张三     19        男
李四     20        男
   ...
一张表就好像一个类,姓名、年龄、性别就好像是这个类里面的字段,整张表就好像是一个List<Person>对象

Class Person{  string name;  int age;  char sex;}

一、什么是数据库? (学习数据库要有过日子的心理)
数据库Database:数据的仓库
数据库就好像一个大超市,货物都要归类区分存放,便于我们管理拿取,我们的数据库也一样
数据库   DBMS(DataBase Management System,数据库管理系统)
2)数据库的特点:海量存储、查找速度快、安全性、、并发性问题控制、数据完整性
1)主键
就是数据行的唯一标识,不会重复的列才能当主键,一个表可以没有主键,但是会非常难以处理,因此没有特殊理由表都要设定主键。
3)数据库里的数据类型
I)二进制数据类型
--》Image:   用来存储非字符和非文本的数据(比如说图片、音乐、视频等。。。)
II)文本数据类型
--》Char: 字符数据包括任意字母、符号或数字字符的组合。(以字节来计算,比如说Char(10) 这个字段里面可以存储10个字节的数据如果存的是‘abc’那么系统会自动在前面加上7个空格,如果是‘哈哈哈’汉字,系统就会加四个空格,因为一个汉字两个字节汉字,这些空格就是如果给的值不够10个字节系统就会自动补够,超出就不行)
--》Varchar:可变长度非Unicode数据(例如varchar(10) 表明这个字段里面可以存储10个字符,以字符个数来计算就是说不管存的是‘1234567890’还是‘哈哈哈哈哈哈哈哈哈哈’都是可以存10个,就不是以字节计算了,同样如果不够十个在内存里存储的时候系统就会自动加上空格,补够10个)
--》nchar:也是以字节来计算,但是不同的是如果nchar(10)给值的时候不够10个字节的话在内存里系统就不会自动加空格补满。
--》nvarchar:和nchar同理
--》text():存储文本信息(指针,可以存2G,就是比较长的文本)推荐使用  varchar(max)
-->Ntext:Nvarchar(max)
III)日期和时间
--》日期和时间在单引号内输入 :Datetime   日期和时间
IIII)数字数据 (该数据仅包含数据,包括证书、负数以及分数)
--》int      整数
-->smallint      整数
--》float   数字 (小数)
-->real    数字
IIIII)货币数据类型
--》money
IIIIII)bit数据类型
--》bit  存储布尔数据类型 (true/false  0/1 一般用来存储性别)
2)创建数据库文件
数据库文件mof  master data file     log  data file
3)数据库的注释
--》   -- 注释的内容
--》   \*
注释内容
      */
4)创建数据库
  --》逻辑名 --》大小   --》增长 --》路径
删除数据库时可能出现的问题 
语法 如果数据库正在使用中是删除不了的,要删除就

use master;  go drop database 数据库名 go

   

4)创建表
create table 表名

   --这里面的字段可以想象成C#里的字段一样,补够这里的字段数据类型要写在前面
字段名 、数据类型、约束(各个字段要用逗号分隔)

--》自动增长使用
    identity(1,1)  --以1为起始值,以1为步长自动增长
--》主键设置
   primary key
-->非空使用
字段名 类型  not null  (表示该字段的值不可以为空)
5)插入
   insert into 表名(字段列表) values(值)
--》查看数据
   select * from 表名
--》修改单个值
   update要修改的表名 set 字段名=值 where 条件
--》修改多个值
    update 要修改的表名 set 字段1=‘值1’,字段2=‘值2’ where 条件
--》追加课使用:字段=字段+值
6)删除表中所有数据:delete from 表名
值删除数据,如果不加where条件表名删除全部数据
   delete只是删除数据,表还在,和drop table不同
--》删除部分数据
  delete from 表名 where 条件
--》删除表
  drop table 表名 
--创建数据库
/*
create database  数据库名
*/
create database MyFirstDataBase
--》默认系统保存数据文件与日志文件
--》逻辑名:就是数据库名
--》文件名:数据库要保存到的位置,注意数据库直接生成的拿两个文件不可以直接保存到系统盘的根目录下,要保存就建一个子文件夹,不建议保存在系统盘
--》大小:初始的存储大小
--》增长:如火存储的内存超过了初始的大小就会以这个值增长,比如说初始大小是10,增长是2,那么当满了10 以后就会自动增长2,当12又满了的时候再增长2,以此类推下去
go
--go不是T-SQL的标准语法
--表示前面的代码作为一段逻辑一起执行
use Doolsql
go
                    --=================实例================---

--创建数据库/*create database 数据库名*/create database MyFirstDataBasego--go不是T-SQL的标准语法--表示前面的代码作为一段逻辑一起执行--删除数据库drop database MyFirstDataBasecreate database MyDataBase2on(  name='MyDataBase2',  filename='E:\DB\MyDataBase2.mdf',  size=10,  filegrowth=2)log on(  name='MyDataBase2_log',  filename='E:\DB\MyDataBase2_log.mdf',  size=5,  filegrowth=2%);--删除drop database MyDataBase2--===============--创建表use MyDataBase2create table MyTable(--identity(1,1)表示以1开始,并且以1作为步长自动增长--primary key表示该字段为主键 Fid int identity(1,1)primary key, FName nvarchar(10) not null, --not null表示该字段部位空 FAge int, FSex bit)--========================drop database MyDataBasecreate database MyDataBasedrop database MyDataBasecreate database MyDataBaseon( name='MyDataBase', filename='E:\DB\MyDataBase.mdf', size=10, filegrowth=2)log on( name='MyDataBase_log', filename='E:\DB\MyDataBase_log.mdf', size=5, filegrowth=2%)--============================use MyDataBasecreate table MyTable(  Fid int identity(1,1)primary key,  FName nvarchar(50),  FSex bit,  FAge int)create table TScore(  FStudentid int identity(1,1)primary key,  FScoreld int,  FEnglish int,  FMath int,)create table TStudent(  FStudentid int identity(1,1)primary key,  FName nvarchar(10),  FAge bit,)create table Teacher( FTeacherid int identity(1,1)primary key, FName nvarchar(10), FAge INT, FSex bit, FSlarary money, FBirthday int)--============--插入数据--查看表结构use Doolsqlgoselect * from dbo.T_student --插入数据(标准) --1/插入全部数据 --insert into 表名 values(值) insert into T_Student values('张三',20,'男','中国') --2/只插入部分字段赋值的插入数据 --insert into 表名(对应字段列表) value(值列表) insert into T_Student    (Name,Age,Sex,Addriss) values    ('李四',50,'男','岛国')   go  --删除数据  delete from T_Student where name='李四'  --把id等于4的张三,的年龄改为26  --update 要修改的表名 set 字段名=值 where 条件  update T_Student set Age=26 where id=4  --修改追加  update T_Student set Name=Name+'疯' where ID=4

图片 1

 

另外,对应表名而言,可以使用下面脚本。在此略过,不做过多介绍!

SQL server学习_函数、类型转换

一、复习
1、建库建表

use master go if db_id('Mydatabase') is null create databese Mydatabase go use Mydatabase go if object_id('MyTable','U') is not null drop table MyTable (  id int identity(1,1)primary key, tName nvarchar(10) not null, tAge int, tGender bit ) go

  

2、增
   insert into 表名(字段1,字段2,....)values(值1,值2...)
注:--》在C#中,SQL语句是一个字符串
   --》SQL中的数字在C#中就是个字符串
   --》SQL中的字符串在C#中是带单引号的字符串

      --》删除数据
          delete from 表名 where 条件
   --》删除表
           drop table 表名
   --》删除数据库
                use master
                go
                 drop database 数据库名
   --》截断表(归零)
         truncate table 表名
            -》将数据全部清空、归零,不记入日记文件中

修改数据
         update 表名 set 字段=值
修改表结构
          alter table 表名 add 
   alter table 表名 drop
   alter table 表名 alter
2、简单查询
  select * from 表名 where 条件
关于条件的范围
    between and(两边都取的到)
in(。。。)
3、模糊查询
模糊查询就是给定一些不完整的条件要查出数据库中的数据的查询
比如说--  要查出谋张表的所有姓张的人、要查出名字以张开头并且名字是两个字的
这时候就可以用到模糊查询了,
模糊查询有常用的几个个统配符 (这些通配符加个[]中括号括起来就表示普通符号)
     --》% 百分号
  % 表示任意个任意字符,可以没有也可以有一个
  --》_ 下划线
  _ 表示一个任意字符
  --》^  非
  ^  和正则表达式的 ‘非’ 很相似,在数据库里这个‘非’要与[^]连起来用,如果没有中括号就是普通字符
  --》[]  中括号
  [] 的第一个功能就是前面说到的当字段、表名等的命名与系统的关键字冲突的时候可以把其用[]括起来
第二个就是匹配字符,注意:[]只匹配里面的第一个字符
4、空值处理
空值就是 null,在SQL server里null表示空,其值是不知道的意思,null和空字符(‘ ’)有区别,空字符(‘ ’)是有值的是有内容的,而Null则是不知道,两者不相同
判断null只能用 is null 和 is null
  --》空值替换   isnull(字段,要替换的值)
比如说要把一张表的空值替换成123
select isnull(检查的字段,要替换的值) from 表名

5、排序   order by
  --》asc 升序排序,不写默认就是升序排序
  --》desc 降序排序
order by 排序的执行顺序永远是在最后执行的,如果有where筛选,order by总是放在后面,这样会提高效率
多列排序(从左到右)
select * from 表名 order by
字段1 [desc],字段2[desc],。。。。

6、数据分组
select语句中可以使用group by 子句将行划分成较小的组,然后使用聚合函数返回每一个组的汇总信息
group by 只返回一个值,一旦使用了group by分组后,前面select列表就不可以写显示多个值的字段,除非是函数列表或者是分组列表成员
比如说要显示所有班级的总人数,同时还要把每个班的所有人的名字一起显示出来,那是不可能的,group by值返回一个值

7、having语句
having语句和where一样都是筛选,但是having和where的执行顺序不同,having是group by的条件对分组后的数据进行筛选,having是对分组后的数据进行筛选的
having要位于group by之后,注意having中(就是其后的判断条件)不能使用未参与分组的列,
8、联合结果集
--》单值结果集
--》行结果集
--》表值结果集
结果集就是select查询出来后的结果
union可以把多个结果集合并成一个结果集
语法:
查询语句1
union
查询语句2

  --》union会自动把财富的数据合并
  --》union all会保留重复数据
一般都是使用union all效率会高点,因为union合并之后会再检索一边里面有没有重复的数据
--》联合要注意一点:多个查询语句之间对应的数据类型要一致

9、字符串函数
--》len():计算字符串的长度(就是字符的个数)
--》datalength():计算字符串字节数,不属于字符串函数,是个系统函数
--》lower()、upper():转小写,大写
--》ltrim():去除字符串左侧的空格
--》rtrim():  去除字符串右侧的空格
--》ltrim(rtrim('   ab     ')):去除字符串左右两侧的空格
--》left()、right():截取字符串,
   select left('abcdefghijk',2);第一个参数是要截取的字符串,第二个参数是截取几个
--》substring(要截取的字符串,开始截取的位置,截取的长度)
这里的字符串的位置是从1开始的没有0

10、日期函数
-->getdate()   获得当前系统时间,年-月-日 时:分:秒:毫秒

--》select day(getdate())   或得当前天数

--》select month(getdate())  获得当前月数

--》select year(getdate())   获得当前年份

--》dateadd(在那个部分增加,增加多少,日期)

--》datediff(单位,开始的时间,结束的时间)     

-->datepart(要返回日期的那一部分,日期时间)

11、类型转换
--》cast(要转换的字段 as 要转换的数据类型)
--》convert(要转换的字段,要转换的数据类型)

 

总结

sqlserver学习_02

一、复习
1) 数据库就是仓库
    -》数据库服务(实例)
    -》数据库
     -》表<-->类 (有一个对应关系)
2)DDL(定义用)
3)创建数据库  

create database 数据库名 on (  name='逻辑名',  filename='文件名',  size=3mb,  filegrowth=1mb  maxsize... )  log on (   name=..... ) use master  --创建数据库前要先use 到主数据库区if db_id('itcast') is null  --然后判断 如果 ‘itcast’这个数据库没有被创建create database itcast    --就创建一个‘itcast’on( name='itcast', filename='E:\db\itcast.mdf')log on( name='itcast_log', filename='E:\db\itcast.ldf')

  4)要新建数据库的时候,当前实例中已经存在了你要新创的数据库,就看一下数据库里有没有你要创建的

if db_id('数据库名') is null create database 数据库名

  5)建表  

create table Student (  id int  name nvarchar(10),  age int,  gender bit )

  

  6、建表有可能已经存在
  --建表前也use 一下 写脚本要养成一个好习惯,没写完一个SQL语句结尾带 分号(;)和go  

use itcastgo if object_id('表名','U') is not null  --U表示用户自定义的表 如果这个表不为null(就是存在了)为代码:if(表存在)(删除表) drop table 表名 6)添加数据  insert into 表名(字段1,字段2.....)values(值1,值2.....)  7)删除   --》删除数据库     drop database 数据库名  --》删除表   drop table 表名  --》删除表数据   delete from 表名 where 条件 (如果没有where判断会把整张表的数据全部删除)  8)修改数据   update 表名 set 列名=值 where 条件  9)(重点)查   select * from 表名   10)使用数据库时,记得use一句代码结束后分号结束一片代码结束后用 go结束11)dbo就是当前数据库表的架构schema(就等价于C#里的命名空间)

   
二、约束
--》数据约束是为了保证数据的完整性(正确性)而实现的一套机制
--》非空约束:not null
1)使用代码添加约束

--》主键约束alter table 表名 add constraint PK_表名_字段名 primary key(字段名)--》唯一约束alter table 表名 add constraint UNQ_表名_字段名 unique(字段名)-->检查约束alter table 表名 add constraint CK_表名_字段名 check(表达式)-->默认约束alter table 表名 add constraint DF_表名_字段名 default(默认值)for 字段--》一次性批量添加约束alter table 表名addconstraint...,constraint...,constraint...,.....删除约束  alter table 表名 drop constraint 约束名

2)创建表的同时就添加约束

use itcastgocreate table MyTable( id identity(1,1) primary key, [unique] int unique, -- 唯一约束 字段名与系统关键字冲突时加[] 不加[]的时候字段中间有空格的话系统会认为是两个字段 name nvarchar(10) not  null,--非空约束 Gender nvarchar(1) check(Gender='男' or Gender='女'), --检查约束 isDel bit default(0) --默认约束)--修改表结构--增加字段alter table student add Fid int;alter table Student add note nvarchar(max)--修改字段的数据类型alter table student alter column sex nvarchar(1)    --删除字段alter table student drop column note--》查看已有约束等select * from sys.objects

3)外键表与主键表

--关于外键(为了节省空间)--首先建一个外键表create table WaiJian( id int identity(1,1) primary key, Fid int, name nvarchar(10))gocreate table ZhuJian( Fid int identity(1,1)primary key, name nvarchar(50))go--关联外键表(这两个键必须要是两个表的主键)alter table WaiJian add constraint FK_WaiJian_ZhuJian_Fid--设置主键foreign key(Fid) references ZhuJian(Fid)

三、检索数据(查询) 

use mastergoselect * from Person--找一招年龄在20岁以下的人select * from Person where FAge<20--找一找20岁以下的女孩select * from Person where FAge<20 and FSex='女'--找一下18到20岁之间的女孩select * from Person where FAge>=18 and FAge<=20 and FSex='女'--加上找20岁以上的男孩 (一般写的语法)select FName 姓名,Fsex 性别,FAge as 年龄from Personwhere  FAge>=18 and FAge<=20 and FSex='女'or  FAge>=20 and FSex='男' ;go--==================--带条件查询--=========================selectprint--这两个都可以用来显示,只是select会以表的形式输出,而print的结果会显示在消息中--=========--以年龄进行排序select * from Person order by FAge  --不屑asc或desc就是默认的升序排序--Top 数字 ;表示在查询出来的结果进行筛选,将浅“数字”个记录显示出来select * from Person order by FAge desc--再找前15个select top 15 * from Person order by FAge desc--再加个条件:性别为女的select top 15 * from Person where FSex='女' order by FAge desc--top后还可以跟一个百分比,表示查询百分之几的数据select top 3 percent * from Person where FSex='女' order by FAge

--=========================
--去除重复(只是显示的时候去除了,并没有真的删除了)

select distinct * from Persom --如果有*号有ID号了系统就认为结果不是相同的select distinct FName,FAge,FSex from Person --没有

--======================
--聚合函数
--最大值max
--最小值min
--平均值avg
--总和sum
--总数count
--求年龄最大的人
select max(FAge) from Person
--最小的
select min(FAge) from Person
--平均
select avg(FAge) from Person
--总数
select count(FName) from Person
--求女的总数
select count(FName) from person where FSex='女'
select * from Person where FSex='女'
--聚合函数使用的几个问题
--avg
--使用avg的时候,如果有一条记录(数据)为null,将不作为计算的数据源
--count的问题
--使用count(字段)的时候,如果针对某个字段,若该字段为null,不计入总数,
--若使用count(*)则忽略该现象,会得到总的数据条数
--求两个字段的平均
select (Fid+FAge)/2 from Person
--条件查询

use MyDataBase15_31goselect * from Person where gender='女' and age<20 and age>18 --条件:gender='女' 并且age<20 并且 age>18--范围select * from Person where gender='女' and age between 18 and 20 --条件:gender='女' 并且age 在18到20之间,包含18 和 20--指定 年龄等于18 20 23select * from Person where gender='女' and age in(18,20,23)  --条件:gender='女' 并且age=18并且age=20并且age=23

其实到了这一步,还没有完,如果一个实例,里面有十几个数据库,那么使用上面这个脚本,我要切换数据库,执行十几次,对于我这种懒人来说,我觉得无法忍受的。那么必须写

一个脚本,将所有数据库全部检查完。本来想用sys.sp_MSforeachdb,但是这个内部存储过程有一些限制,遂写了下面脚本。

图片 2

前言

 

 DATALENGTH(name) = 2* LEN(name)

SELECT name , DATALENGTH(name) AS NAME_BYTES , LEN(name) AS NAME_CHARACTERFROM sys.columnsWHERE object_id = OBJECT_ID('TEST_COLUMN'); clip_image001

 

USE TEST;GO --表TEST_COLUMN中两个字段都包含有空格CREATE TABLE TEST_COLUMN ( "ID " INT IDENTITY (1,1), [Name ] VARCHAR(32), [Normal] VARCHAR(32));GO --表[TEST_TABLE ]中包含空格, 里面对应三个字段,一个前面包含空格,一个字段中间包含空格,一个字段后面包含空格。CREATE TABLE [TEST_TABLE ]( [ F_NAME] NVARCHAR(32), [M NAME] NVARCHAR(32), [L_NAME ] NVARCHAR(32))GO

    DATALENGTH(name) = 2* LEN(name)

SELECT * FROM sys.columns WHERE NAME LIKE ' %' --字段前面包含空格。

 

DECLARE @db_name NVARCHAR(32);DECLARE @sql_text NVARCHAR(MAX); DECLARE @db TABLE ( database_name NVARCHAR(64)); INSERT INTO @dbSELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id !=2; WHILE (1=1)BEGIN SELECT TOP 1 @db_name = database_name FROM @db ORDER BY 1; IF @@ROWCOUNT = 0 RETURN; SET @sql_text =N'USE ' + @db_name +'; SELECT ''' + @db_name + ''' as database_name, name, DATALENGTH(name) as table_name_bytes, LEN(name) as table_name_character, type_desc,create_date,modify_date FROM sys.tables WHERE DATALENGTH(name) != LEN(name) * 2; '; PRINT(@sql_text); EXECUTE(@sql_text); DELETE FROM @db WHERE database_name=@db_name; END

 

DECLARE @db_name NVARCHAR(32);DECLARE @sql_text NVARCHAR(MAX); DECLARE @db TABLE ( database_name NVARCHAR(64)); IF OBJECT_ID('tempdb.dbo.#TabColums') IS NOT NULL DROP TABLE dbo.#TabColums; CREATE TABLE #TabColums( object_id INT , column_id INT); INSERT INTO @dbSELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id !=2; WHILE (1=1)BEGIN SELECT TOP 1 @db_name = database_name FROM @db ORDER BY 1; IF @@ROWCOUNT = 0 RETURN; SET @sql_text =N'USE ' + @db_name +'; TRUNCATE TABLE #TabColums; INSERT INTO #TabColums SELECT object_id , column_id FROM sys.columns WHERE DATALENGTH(name) != LEN(name) * 2; SELECT ''' + @db_name + ''' AS DatabaseName, TL.name AS TableName , C.name AS FieldName , T.name AS DataType , DATALENGTH(C.name) AS COLUMN_DATALENGTH , LEN(C.name) AS COLUMN_LENGTH , CASE WHEN C.max_length = -1 THEN ''Max'' ELSE CAST(C.max_length AS VARCHAR) END AS Max_Length , CASE WHEN C.is_nullable = 0 THEN ''×'' ELSE ''√'' END AS Is_Nullable , C.is_identity , ISNULL(M.text, '''') AS DefaultValue , ISNULL(P.value, '''') AS FieldComment FROM sys.columns C INNER JOIN sys.types T ON C.system_type_id = T.user_type_id LEFT JOIN dbo.syscomments M ON M.id = C.default_object_id LEFT JOIN sys.extended_properties P ON P.major_id = C.object_id AND C.column_id = P.minor_id INNER JOIN sys.tables TL ON TL.object_id = C.object_id INNER JOIN #TabColums TC ON C.object_id = TC.object_id AND C.column_id = TC.column_id ORDER BY C.column_id ASC;'; PRINT(@sql_text); EXECUTE(@sql_text); DELETE FROM @db WHERE database_name=@db_name; END TRUNCATE TABLE #TabColums;DROP TABLE #TabColums;
DECLARE @db_name  NVARCHAR(32);

DECLARE @sql_text NVARCHAR(MAX);

 

DECLARE @db TABLE 

(

    database_name  NVARCHAR(64)

);

 

IF OBJECT_ID('tempdb.dbo.#TabColums') IS NOT NULL

 

    DROP TABLE dbo.#TabColums;

 

CREATE TABLE #TabColums

(

    object_id            INT ,

    column_id            INT

);

 

 

INSERT INTO @db

SELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id !=2;

 

 

WHILE (1=1)

BEGIN

    SELECT TOP 1 @db_name = database_name FROM @db ORDER BY 1;

    

    IF @@ROWCOUNT = 0 RETURN;

 

    SET @sql_text =N'USE ' + @db_name +';

                     TRUNCATE TABLE #TabColums;

 

    

                    INSERT INTO #TabColums

                    SELECT  object_id ,

                            column_id

                    FROM    sys.columns

                    WHERE   DATALENGTH(name) != LEN(name) * 2;

                

                    SELECT  ''' + @db_name  + ''' AS DatabaseName,

                            TL.name AS TableName ,

                            C.name AS FieldName ,

                            T.name AS DataType ,

                            DATALENGTH(C.name) AS COLUMN_DATALENGTH ,

                            LEN(C.name) AS COLUMN_LENGTH ,

                            CASE WHEN C.max_length = -1 THEN ''Max''

                                    ELSE CAST(C.max_length AS VARCHAR)

                            END AS Max_Length ,

                            CASE WHEN C.is_nullable = 0 THEN ''×''

                                    ELSE ''√''

                            END AS Is_Nullable ,

                            C.is_identity ,

                            ISNULL(M.text, '''') AS DefaultValue ,

                            ISNULL(P.value, '''') AS FieldComment

                    FROM    sys.columns C

                            INNER JOIN sys.types T ON C.system_type_id = T.user_type_id

                            LEFT  JOIN dbo.syscomments M ON M.id = C.default_object_id

                            LEFT  JOIN sys.extended_properties P ON P.major_id = C.object_id

                                                                    AND C.column_id = P.minor_id

                            INNER JOIN sys.tables TL ON TL.object_id = C.object_id

                            INNER JOIN #TabColums TC ON C.object_id = TC.object_id

                                                        AND C.column_id = TC.column_id

                    ORDER BY C.column_id ASC;';

        PRINT(@sql_text);

 

        EXECUTE(@sql_text);

 

        DELETE FROM @db WHERE database_name=@db_name;

 

END

 

TRUNCATE TABLE #TabColums;

DROP TABLE #TabColums;

实现方法:

 

其实到了这一步,还没有完,如果一个实例,里面有十几个数据库,那么使用上面这个脚本,我要切换数据库,执行十几次,对于我这种懒人来说,我觉得无法忍受的。那么必须写

 

不包含尾随空格。所以这个脚本是无法排查表名或字段名前面包含空格的。如果要排查这种情况,就需要使用下面SQL脚本:

另外,对应表名而言,可以使用下面脚本。在此略过,不做过多介绍!

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

 

本文主要给大家介绍的是关于SQL Server查找包含空格的表和列的相关内容,为什么会有这篇文章,是因为最近发现一个数据库中的某个表有个字段名后面包含了一个空格,这个空格引起了一些小问题,一般出现这种情况,是因为创建对象时,使用双引号或双括号的时候,由于粗心或手误多了一个空格,如下简单案例所示:

 

那么要如何找出表名或字段名包含空格的相关信息呢? 不管是常规方法还是正则表达式,这个都会效率不高。我们可以用一个取巧的方法,就是通过字段的字符数和字节数的规律来判断,如果没有包含空格,那么列名的字节数和字符数满足下面规律:

 

原理是这样的,保存这些元数据的字段类型为sysname ,其实这个系统数据类型,用于定义表列、变量以及存储过程的参数,是nvarchar(128)的同义词。所以一个字母占2个字节。那么我们安装这个规律写了一个脚本来检查数据中那些表名或字段名包含空格。方便巡检。如下测试所示

那么为什么表名TEST_TABLE的三个字段里面,前面包含空格与与中间包含空格都识别不出来呢?这个与数据库的LEN函数有关系,LEN函数返回指定字符串表达式的字符数,其中

那么为什么表名TEST_TABLE的三个字段里面,前面包含空格与与中间包含空格都识别不出来呢?这个与数据库的LEN函数有关系,LEN函数返回指定字符串表达式的字符数,其中

不包含尾随空格。所以这个脚本是无法排查表名或字段名前面包含空格的。如果要排查这种情况,就需要使用下面SQL脚本(中间包含空格在此略过,这个不符合命名规则):

一个脚本,将所有数据库全部检查完。本来想用sys.sp_MSforeachdb,但是这个内部存储过程有一些限制,遂写了下面脚本。

 

 

 

 

 

最近发现一个数据库中的某个表有个字段名后面包含了一个空格,这个空格引起了一些小问题,一般出现这种情况,是因为创建对象时,使用双引号或双括号的时候,由于粗心或手误多了一个空格,如下简单案例所示:

 

那么要如何找出表名或字段名包含空格的相关信息呢? 不管是常规方法还是正则表达式,这个都会效率不高。我们可以用一个取巧的方法,就是通过字段的字符数和字节数的规律来判断,如果没有包含空格,那么列名的字节数和字符数满足下面规律(表名也是如此):

 

 

原理是这样的,保存这些元数据的字段类型为sysname **,其实这个系统数据类型,用于定义表列、变量以及存储过程的参数,是nvarchar(128)的同义词。所以一个字母占2个字节。**那么我们安装这个规律写了一个脚本来检查数据中那些表名或字段名包含空格。方便巡检。如下测试所示

 

IF OBJECT_ID('tempdb.dbo.#TabColums') IS NOT NULL

    DROP TABLE dbo.#TabColums;

 

CREATE TABLE #TabColums

(

    object_id            INT ,

    column_id            INT

)

 

INSERT INTO #TabColums

SELECT  object_id ,

        column_id

FROM    sys.columns

WHERE   DATALENGTH(name) != LEN(name) * 2

 

 

SELECT 

    TL.name AS TableName,

    C.Name AS FieldName,

    T.Name AS DataType,

    DATALENGTH(C.name) AS COLUMN_DATALENGTH,

    LEN(C.name) AS COLUMN_LENGTH,

    CASE WHEN C.Max_Length = -1 THEN 'Max' ELSE CAST(C.Max_Length AS VARCHAR) END AS Max_Length,

    CASE WHEN C.is_nullable = 0 THEN '×'  ELSE N'√' END AS Is_Nullable,

    C.is_identity,

    ISNULL(M.text, '')  AS  DefaultValue,

    ISNULL(P.value, '') AS FieldComment

    

FROM sys.columns  C

INNER JOIN  sys.types T ON C.system_type_id = T.user_type_id

LEFT  JOIN dbo.syscomments M ON M.id = C.default_object_id

LEFT  JOIN sys.extended_properties P ON P.major_id = C.object_id AND C.column_id = P.minor_id 

INNER JOIN sys.tables TL ON TL.object_id = C.object_id

INNER JOIN #TabColums TC ON C.object_id = TC.object_id AND c.column_id = TC.column_id

ORDER BY C.Column_Id ASC

 

 

 

SELECT * FROM sys.columns WHERE NAME LIKE ' %'  --字段前面包含空格。

图片 3

 

图片 4

DECLARE @db_name  NVARCHAR(32);

DECLARE @sql_text NVARCHAR(MAX);

 

DECLARE @db TABLE 

(

    database_name  NVARCHAR(64)

);

 

 

 

INSERT INTO @db

SELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id !=2;

 

 

WHILE (1=1)

BEGIN

    SELECT TOP 1 @db_name = database_name FROM @db ORDER BY 1;

    

    IF @@ROWCOUNT = 0 RETURN;

 

    SET @sql_text =N'USE ' + @db_name +';

 

                                    

                    SELECT ''' + @db_name  + '''  as database_name, name, 

                         DATALENGTH(name) as table_name_bytes,

                         LEN(name)          as table_name_character,

                         type_desc,create_date,modify_date 

                    FROM sys.tables

                    WHERE   DATALENGTH(name) != LEN(name) * 2;

                   ';

        PRINT(@sql_text);

 

        EXECUTE(@sql_text);

 

        DELETE FROM @db WHERE database_name=@db_name;

 

END

 

 

 

 

 

 

 

 

 

USE TEST;

GO

 

--表TEST_COLUMN中两个字段都包含有空格

CREATE TABLE TEST_COLUMN 

(

    "ID  "    INT IDENTITY (1,1),

    [Name ]   VARCHAR(32),

    [Normal]  VARCHAR(32)

);

GO

 

--表[TEST_TABLE ]中包含空格, 里面对应三个字段,一个前面包含空格(后面详细阐述),一个字段中间包含空格,一个字段后面包含空格。

CREATE TABLE [TEST_TABLE ]

(

 

    [ F_NAME]        NVARCHAR(32),

    [M NAME]         NVARCHAR(32),

    [L_NAME ]        NVARCHAR(32)

)

GO
SELECT  name ,

        DATALENGTH(name) AS NAME_BYTES ,

        LEN(name)         AS NAME_CHARACTER

FROM    sys.columns

WHERE   object_id = OBJECT_ID('TEST_COLUMN');