sqlserver tips
浏览数：19 /
时间：2015年06月12日
方括号内的表示一个对象名（视图，存储过程，表 等）、列名；
正常使用时，加不加一样，但是如果对象名是保留字的话，比如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.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‘)
/****** 删除出所有的表，或者视图******/
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
