特殊sql.docVIP

特殊sql --查询指定表的指定字段的名称、字段类型、是否为空、字段长度 select syscolumns.name as 字段名称,systypes.name as 字段类型,syscolumns.isnullable,syscolumns.length from syscolumns,systypes where syscolumns.xusertype=systypes.xusertype and syscolumns.id =object_id(huiyzl) --单独查询表的递增字段 select [name] from syscolumns where id=object_id(Nwldwzl) and columnproperty(id,name,IsIdentyty)=1 --查询一个表的所有约束条件，包括主、外键 exec sp_helpconstrainthuiyzl --查询指定的字段在哪个表中 select a.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype=u and b.name=zhiyname select * from sysobjects where xtype=u --查看数据库信息 select * from master.dbo.sysdatabases --查看当前数据库大小 exec sp_spaceused @updateusage = TRUE --复制表结构(将会员资料表结构复制到huiyzl11表中) SELECT * into huiyzl11 from huiyzl where 11 --跨数据库插入数据 insert into cr_yppf8_cw.dbo.wldwzl_moreinfo select * from a.dbo.wldwzl_moreinfo --查询指定的表中的会部字段 Select name from syscolumns Where ID=OBJECT_ID(wldwzl) --查询存储过程中的字段 --列：查询 包含 麻黄 两个字的存储过程 SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE %麻黄% AND ROUTINE_TYPE=PROCEDURE select name from sysobjects o, syscomments s where o.id = s.id and text like %麻黄% and o.xtype = P --建立与zhiyzl表字段一样的表 SELECT * into Tabletwo from zhiyzl where 11 --用B表中zhiyname字段更新A表中zhiyname字段 update a set a.zhiyname=b.zhiyname from Tabletwo a,zhiyzl b where a.zhiyid=b.zhiyid --查询zhiyzl表中的字段 select name, from syscolumns where id=(select max(id) from sysobjects where xtype=u and name=zhiyzl) DBCC SHRINKDATABASE(aaa) --收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles DBCC SHRINKFILE(1) --重建数据库索引： USE aaa --Enter the name of the database you want to reindex DECLARE @TableName varchar(4000) DECLARE TableCursor CURSOR FOR SELECT Table_Schema+.+Table_Name FROM information_schema.tables WHERE table_type = base table OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName, ,90) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor