实用sql语句：查询结果导出到excel,收缩数据库.doc

-查询结果导出到excelSqlServerexec master.xp_cmdshell bcp select * from mydatabase.dbo.mytable queryout c:temp.xls -c -q -S. -Usa -P1-excel导入到SqlServerBULK INSERT temp1 FROM c:temp1.xls -收缩数据库-首先截断事务日志 backup log mydatabase with no_log -收缩数据库 dbcc shrinkdatabase(mydatabase,0)-查SqlServer视图sqlselect text from syscomments where id=object_id(reportsbaseview)select * from information_schema.views-查SqlServer:表名select * from information_schema.tables where table_name like %MYTABLE%-查Oracle:表名select * from sys.all_tables where table_name = MYTABLE-查Sqlserver列名select * from information_schema.columns where table_name = MYTABLE-查Orable:列名select * from sys.all_tab_cols where table_name = MYTABLE-查Sqlserver列描述SELECT *FROM :fn_listextendedproperty (NULL, user , dbo, table, MYTABLE, column, default)-查Orable:列描述select * from sys.all_col_comments where table_name = MYTABLE-为查询结果添加序号（pkId必须是整数类型）select number1=(select count(userId) from tuserset as t2 where t2.pkId=t1.pkId),userId,setName from tuserset as t1-插入100条测试记录declare i intset i=500while (i600)begininsert into MYTABLE (invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus,projecttypeid)values(i,i,i,100001,136,0,11)set i=i+1end-查询每个表有几条记录declare colId varchar(50)DECLARE detailCustom_Cursor CURSOR FORselect top 90 table_name as tableName from information_schema.tables order by tableName-select table_name as tableName from information_schema.tables where table_name not in (select top 90 table_name from information_schema.tables order by table_name)OPEN detailCustom_CursorFETCH NEXT FROM detailCustom_Cursorinto colIdbegin tran t1declare sql varchar(8000)set sql = WHILE FETCH_STATUS = 0BEGINif(len(sql)=7800)begin set sql = sql + select +colId+ as tableName,count(*) as data from +colId set sql = sql + union all endFETCH NEXT FROM detailCustom_Cursorinto colIdENDset sql = sql + select -1,-1exec(sql)commit tran t1CLOSE detailCustom_CursorDEALLOCATE detailCustom_CursorC#与sql相关1,取1条sql语句除了某几列，其他列的都查出来，在aspx页面中写：protected string GetExtraSql(string tableName,string exceptionColumns)string returnString = String.Empty;string sql = select top 1 * from +tableName;DataSet temp = wdxl.Commfile.Dblib.GetDataSet(sql);if(temp!=null)sql = select ;DataTable tableObj = temp.Tables0;for(int i=0;itableObj.Columns.Count;i+)/如果找不到则添加if(!StringInArray(exceptionColumns,tableObj.Columnsi.ToString()sql += tableObj.Columnsi+,;int flag = sql.Length;sql = sql.Substring(0,flag-1);sql += from +tableName;returnString = sql;return returnString;protected bool StringInArray(string arrayObj,string data)bool returnValue = false;for (int i=0;iarrayObj.Length;i+)if(arrayObji.ToUpper()=data.ToUpper()returnValue = true;break;return returnValue;-查询另外一个数据库服务器的表数据:SELECT * FROM OPENDATASOURCE(SQLOLEDB,Data Source=2;database=mydatabase;user id=sa;Password=1).mydatabase.dbo.mytable-在sqlserver2005执行上面语句可能报权限错误，需要开启一下相关参数：exec sp_configure show advanced options,1RECONFIGURE WITH OVERRIDEexec sp_configure Ad Hoc Distributed Queries,1RECONFIGURE WITH OVERRIDE-另一种方式：用链接服务器查询另外一个数据库服务器的表数据-建立链接服务器EXEC sp_addlinkedserver mycomputer, , MSDASQL, NULL, NULL, DRIVER=SQL Server;SERVER=03;UID=sa;PWD=1;GO-建立链接服务器登录映射exec sp_addlinkedsrvloginrmtsrvname=mycomputer,useself=false,locallogin=Administrators,rmtuser=sa,rmtpassword=1select * from mycomputer.mydatabase.dbo.users-SqlServer批量改某一列的类型，temp1为零时表declare col1 varchar(50)declare col2 varchar(50)DECLARE detailCustom_Cursor CURSOR FORselect column_name col1,table_name col2 from information_schema.columns where data_type = decimal OPEN detailCustom_CursorFETCH NEXT FROM detailCustom_Cursorinto col1,col2begin tran t1WHILE FETCH_STATUS = 0BEGINbegin declare sql varchar(255) set sql = alter table +col2+ alter column +col1+ int null insert into temp1 values (sql)endFETCH NEXT FROM detailCustom_Cursorinto col1,col2ENDcommit tran t1CLOSE detailCustom_CursorDEALLOCATE detailCustom_Cursor-为查询结果添加一列序号Select (select Count(*) from FIELDDICTIONARY T where T.FIELDDICTIONARYid=FIELDDICTIONARY.FIELDDICTIONARYid) as Nbr ,* From FIELDDICTIONARY order by nbr asc-Oracle中的newid()SELECT SYS_GUID() FROM DUAL-监控oracle中占用磁盘I/O较高的sql语句select a.username,b.block_gets,b.consistent_gets,b.physical_reads,b.block_changes,b.consistent_changes,c.sql_textfrom v$session a,v$sess_io b,v$sqltext cwhere a.sid=b.sid AND a.sql_address=c.addressAND a.username IS NOT NULLorder by a.username,c.sql_id,c.piece-Oracle创建及获取表的描述，及字段描述信息-创建表描述COMMENT ON TABLE myTable is 表的描述信息-创建字段描述COMMENT ON COLUMN myTable.ID is 字段的描述信息-取得表描述select * from user_tab_comments where comments is not null-取得字段描述select * from user_col_comments where comments is not null-SqlServer创建及获取表的描述，及字段描述信息-创建表描述EXEC sp_addextendedproperty 描述类别(可自定义), 表的描述信息, user, dbo, table, myTableName, null, null-创建字段描述EXEC sp_addextendedproperty 描述类别(可自定义), 字段的描述信息,