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

 

/******按照降序尋找表中的行數 ******//******按照降序尋找表大小，所佔空間，行數 ******//******尋找表資訊，查詢出來的列分別是表類型，表名，表描述，列名，列描述，類型名，最大長度，是不是遞增，可否為空白，是不是計算資料行，計算文法。 ******/ /****** 索引表資訊，查詢出來的列分別是，表名，索引名，是不是唯一索引，索引類型，列名，降序 ******/ /****** 查詢關聯表資訊。t1.name as 表名，t2.name as 引用的表名。 列名，引用列名 ******/ /******按照降序尋找表中的行數 ******/SELECT   a.name, b.rowsFROM      sysobjects AS a INNER JOIN                 sysindexes AS b ON a.id = b.idWHERE   (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 forselect ‘alter table [‘+ object_name(parent_obj) + ‘] drop constraint [‘+name+‘]; ‘from sysobjectswhere xtype = ‘F‘open c1declare @c1 varchar(8000)fetch next from c1 into @c1while(@@fetch_status=0)beginexec(@c1)fetch next from c1 into @c1endclose c1deallocate c1 -------有時候會報錯，可以把裡面語句複製出來直接執行use SBE_ADMS30Dtestgodeclare @sql varchar(8000)while (select count(*) from sys.objects where type=‘U‘)>0beginSELECT @sql=‘drop table ‘ +  nameFROM sysobjectsWHERE (type = ‘U‘)exec(@sql)endgo/****** 刪除索引，刪除表 ******/ /****** 選擇出所有的表，或者視圖******/ --use 資料庫名稱  SELECT ‘drop table ‘ +  nameFROM sysobjectsWHERE (type = ‘U‘)/****** 選擇出所有的表，或者視圖******/ /****** 刪除所有的表，或者視圖******/ --use 資料庫名稱  SELECT ‘drop view ‘ +  nameFROM sysobjectsWHERE (type = ‘V‘)/****** 刪除出所有的表，或者視圖******/ 

　　

sqlserver tips