SQL Server Tips

The square brackets represent an object name (view, stored procedure, table, etc.), column name;
Normal use, add the same, but if the object name is reserved words, such as cascade, it must be added, but it is recommended not to keep the word as the object name

If object_id (' [Huang] ') is not null drop table [Huang]
Go
CREATE TABLE [Huang] ([customer ID] int,[source] nvarchar (40), [Acquisition Time] datetime)
Insert [Huang]
Select 1111, ' Market d ', ' 2014-05-05 ' UNION ALL
Select 1112, ' Market d ', ' 2014-05-03 ' UNION ALL
Select 1112, ' Fairs ', ' 2014-05-03 ' UNION ALL
Select 1111, ' Fairs ', ' 2014-05-05 ' UNION ALL
Select 1111, ' Sohu ', ' 2014-05-05 ' UNION ALL
Select 1113, ' Sohu ', ' 2014-05-06 '
--------------Start Querying--------------------------

Select a.[Customer ID],
Stuff (SELECT ', ' +[source ' from [Huang] b
Where b.[customer id]=a.[customer ID]
FOR XML Path (")), 1, 1, ') ' source '
From [Huang] A
GROUP BY a.[Customer ID]

SELECT hobby+ ', ' from student for XML PATH (')

SQL Server Chinese garbled, setting database properties options Collation Chinese_prc_ci_as

/****** the number of rows in descending lookup table ******//****** in descending lookup table size, occupied space, number of rows ******//****** lookup table information, the columns queried are table type, table name, table description, column name, column description, type name, maximum length, is not incremented , can be null, is not a computed column, the calculation syntax. //****** Index Table information, the columns queried are, table name, index name, is not a unique index, index type, column name, descending ******//****** Query association table information. T1.name as table name, t2.name as reference table name. Column name, reference column name ******//****** the number of rows in descending lookup table ******/select a.name, B.rowsfrom sysobjects as a INNER JOIN Sysi Ndexes as B on a.id = b.idwhere (A.type = ' u ') and (B.indid in (0, 1)) Order by B.rows desc/****** Find the number of rows in the table in descending order ******//*** Find table size in descending order, occupy space, number of rows ******/create table #Data (name varchar), row varchar (+), reserved varchar (), Data varchar (  (+), index_size varchar (+), unused varchar (+)) declare @name varchar (+) DECLARE cur cursor for select name from sysobjects where xtype= ' u ' ORDER by name Open cur fetch NEXT from cur to @name while @ @fetch_status =0 begin insert  into #data exec sp_spaceused @name print @name fetch NEXT from cur to @name end close cur deallocate cur CREATE TABLE #DataNew (namevarchar (+), row int,reserved int,data int,index_size int,unused int) insert INTO #dataNew select Name,convert (Int,row) A S 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 #data New ORDER BY data desc/****** in descending order to find the table size, the space occupied, the number of rows ******//****** lookup table information, the queried columns are table type, table name, table description, column name, column description, type name, maximum length, is not incremented, can be empty, is not a computed column, the calculation syntax. /select Tb.type,tb.name as TableName, P1.value as Tabledescription, c.name as columnname,p2.value as Columndescri Ption,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_i d=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.comput Ed_columns cc on cc.object_id=tb.object_id and cc.column_id=c.column_id left joins Sys.schemas s on Tb.schema_id=s.schema _id where Tb.type in (' U ', ' V ')/****** lookup table information, the queried columns are table type, table name, table description, column name, column description, type name, maximum length, is not incremented, can be null, is not computed column, calculation syntax. //****** Index Table information, the columns queried are, table name, index name, is not a unique index, index type, column name, descending ******/Select TB. Name as TableName, ix.name as IndexName, ix.is_unique_constraint as Isuniqueconstraint, Ix.type_desc as Indextype, Ix.is_u Nique as isunique,ix.is_primary_key as Isprimary, c.name as ColumnName, Ic.is_descending_key as IsDescending, s.name as [s   Chema] from sys.indexes IX INNER JOIN Sys.index_columns ICS 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_idINNER 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/****** Index Table information, the columns queried are, table name, index name, is not a unique index, index type, column name, descending ******//****** Query association table information. T1.name as table name, t2.name as reference table name. Column name, reference column name ******/Select Sys.objects.name as Associationname, t1.name as TableName, t2.name as Referencetablenam E, c1.name as ColumnName, c2.name as Referencecolumnname from sys.objects inner join Sys.foreign_key_columns C on S ys.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 joins 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 '/****** queries the associated table information. T1.name as table name, t2.name as reference table name. Column name, reference column name ******//****** Delete index, delete table ******/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) Beginex EC (@c1) fetch next from C1 into @c1endclose c1deallocate C1-------Sometimes error, you can copy the inside statements directly execute use Sbe_adms30dtestgodeclare @ SQL varchar (8000) while (select COUNT (*) from sys.objects where type= ' U ') >0beginselect @sql = ' drop table ' + Namefrom sy Sobjectswhere (type = ' U ') exec (@sql) endgo/****** Delete index, delete table ******//****** Select out all tables, or view ******/--use database name select ' Drop Ta Ble ' + namefrom sysobjectswhere (type = ' U ')/****** select out all tables, or view ******//****** Delete all tables, or view ******/--use database name select '  Drop View ' + Namefrom sysobjectswhere (type = ' V ')/****** Delete all tables, or view ******/

　　

SQL Server Tips