sqlserver tips

方括号内的表示一个对象名（视图，存储过程，表 等）、列名；
正常使用时，加不加一样，但是如果对象名是保留字的话，比如cascade，就必须加；不过建议不用保留字作为对象名

if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([客户ID] int,[来源] nvarchar(40),[采集时间] datetime)
insert [huang]
select 1111,'赶集d','2014-05-05' union all
select 1112,'赶集d','2014-05-03' union all
select 1112,'赶集','2014-05-03' union all
select 1111,'赶集','2014-05-05' union all
select 1111,'搜狐','2014-05-05' union all
select 1113,'搜狐','2014-05-06'
--------------开始查询--------------------------

select a.[客户ID],
stuff((select ','+[来源] from [huang] b
where b.[客户ID]=a.[客户ID]
for xml path('')),1,1,'') '来源'
from [huang] a
group by a.[客户ID]

SELECT hobby+',' FROM student  FOR XML PATH('')

 

sqlserver中文乱码，设置数据库属性 options collation Chinese_PRC_CI_AS

 

?

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 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149

/******按照降序查找表中的行数 ******/ /******按照降序查找表大小，所占空间，行数 ******/ /******查找表信息，查询出来的列分别是表类型，表名，表描述，列名，列描述，类型名，最大长度，是不是递增，可否为空，是不是计算列，计算语法。 ******/ /****** 索引表信息，查询出来的列分别是，表名，索引名，是不是唯一索引，索引类型，列名，降序 ******/ /****** 查询关联表信息。t1.name as 表名，t2.name as 引用的表名。 列名，引用列名 ******/     /******按照降序查找表中的行数 ******/ SELECT   a.name, b.rows FROM      sysobjects AS a INNER JOIN                  sysindexes AS b ON a.id = b.id WHERE   (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC /******按照降序查找表中的行数 ******/     /******按照降序查找表大小，所占空间，行数 ******/ create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))    declare @name varchar(100) declare cur cursor  for     select name from sysobjects where xtype='u' order by name open cur fetch next from cur into @name while @@fetch_status=0 begin     insert into #data     exec sp_spaceused   @name     print @name        fetch next from cur into @name end close cur deallocate cur    create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)    insert into #dataNew select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data, convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data     select * from #dataNew order by data desc   /******按照降序查找表大小，所占空间，行数 ******/     /******查找表信息，查询出来的列分别是表类型，表名，表描述，列名，列描述，类型名，最大长度，是不是递增，可否为空，是不是计算列，计算语法。 ******/   select         tb.type,         tb.name as TableName,         p1.value as TableDescription,         c.name as ColumnName,         p2.value as ColumnDescription,         tp.name as TypeName,         c.max_length as MaxLength,         c.is_identity as IsIdentity,         c.is_nullable as Nullable,         c.is_computed as IsComputed,         cc.definition as Fomula,         c.Precision,         c.Scale,         s.name as [Schema]   from sys.columns c   inner join sys.objects tb on c.object_id=tb.object_id   inner join sys.types tp on c.system_type_id=tp.system_type_id  and c.user_type_id=tp.user_type_id   left join sys.extended_properties p1 on p1.major_id=tb.object_id and p1.minor_id=0 and p1.name='MS_Description'   left join sys.extended_properties p2 on p2.major_id=tb.object_id and p2.minor_id=c.column_id and p2.name='MS_Description'   left join sys.computed_columns cc on cc.object_id=tb.object_id and cc.column_id=c.column_id   left join sys.schemas s on tb.schema_id=s.schema_id where tb.type in ('U','V') /******查找表信息，查询出来的列分别是表类型，表名，表描述，列名，列描述，类型名，最大长度，是不是递增，可否为空，是不是计算列，计算语法。 ******/     /****** 索引表信息，查询出来的列分别是，表名，索引名，是不是唯一索引，索引类型，列名，降序 ******/      select              tb.Name as TableName,              ix.name as IndexName,              ix.is_unique_constraint as IsUniqueConstraint,              ix.type_desc as IndexType,              ix.is_unique as IsUnique,ix.is_primary_key as IsPrimary,              c.name as ColumnName,              ic.is_descending_key as IsDescending,              s.name as [Schema]       from sys.indexes ix       inner join sys.index_columns ic on ix.object_id=ic.object_id  and ix.index_id=ic.index_id       inner join sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id       inner join sys.tables tb on tb.object_id=ix.object_id       left join sys.schemas s on tb.schema_id=s.schema_id where ix.type!=0 /****** 索引表信息，查询出来的列分别是，表名，索引名，是不是唯一索引，索引类型，列名，降序 ******/     /****** 查询关联表信息。t1.name as 表名，t2.name as 引用的表名。 列名，引用列名 ******/      select            sys.objects.name as AssociationName,            t1.name as TableName,            t2.name as ReferenceTableName,            c1.name as ColumnName,            c2.name as ReferenceColumnName       from sys.objects       inner join sys.foreign_key_columns c on sys.objects.object_id=c.constraint_object_id       inner join sys.tables t1 on t1.object_id=c.parent_object_id       inner join sys.tables t2 on t2.object_id=c.referenced_object_id       inner join sys.columns c1 on c1.object_id=t1.object_id and c.parent_column_id=c1.column_id       inner join sys.columns c2 on c2.object_id=t2.object_id and c.referenced_column_id=c2.column_id      where sys.objects.type_desc='FOREIGN_KEY_CONSTRAINT' /****** 查询关联表信息。t1.name as 表名，t2.name as 引用的表名。 列名，引用列名 ******/    /****** 删除索引，删除表 ******/ DECLARE c1 cursor for select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; ' from sysobjects where xtype = 'F' open c1 declare @c1 varchar(8000) fetch next from c1 into @c1 while(@@fetch_status=0) begin exec(@c1) fetch next from c1 into @c1 end close c1 deallocate c1    -------有时候会报错，可以把里面语句复制出来直接执行 use SBE_ADMS30Dtest go declare @sql varchar(8000) while (select count(*) from sys.objects where type='U')>0 begin SELECT @sql='drop table ' +  name FROM sysobjects WHERE (type = 'U')   exec(@sql) end go /****** 删除索引，删除表 ******/   /****** 选择出所有的表，或者视图******/ --use 数据库名称  SELECT 'drop table ' +  name FROM sysobjects WHERE (type = 'U') /****** 选择出所有的表，或者视图******/   /****** 删除所有的表，或者视图******/ --use 数据库名称  SELECT 'drop view ' +  name FROM sysobjects WHERE (type = 'V') /****** 删除出所有的表，或者视图******/