實用sql語句：查詢結(jié)果導(dǎo)出到excel,收縮數(shù)據(jù)庫.doc

-查詢結(jié)果導(dǎo)出到excelSqlServerexec master.xp_cmdshell bcp select * from mydatabase.dbo.mytable queryout c:temp.xls -c -q -S. -Usa -P1-excel導(dǎo)入到SqlServerBULK INSERT temp1 FROM c:temp1.xls -收縮數(shù)據(jù)庫-首先截斷事務(wù)日志 backup log mydatabase with no_log -收縮數(shù)據(jù)庫 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-為查詢結(jié)果添加序號（pkId必須是整數(shù)類型）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相關(guān)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;-查詢另外一個數(shù)據(jù)庫服務(wù)器的表數(shù)據(jù):SELECT * FROM OPENDATASOURCE(SQLOLEDB,Data Source=2;database=mydatabase;user id=sa;Password=1).mydatabase.dbo.mytable-在sqlserver2005執(zhí)行上面語句可能報權(quán)限錯誤，需要開啟一下相關(guān)參數(shù)：exec sp_configure show advanced options,1RECONFIGURE WITH OVERRIDEexec sp_configure Ad Hoc Distributed Queries,1RECONFIGURE WITH OVERRIDE-另一種方式：用鏈接服務(wù)器查詢另外一個數(shù)據(jù)庫服務(wù)器的表數(shù)據(jù)-建立鏈接服務(wù)器EXEC sp_addlinkedserver mycomputer, , MSDASQL, NULL, NULL, DRIVER=SQL Server;SERVER=03;UID=sa;PWD=1;GO-建立鏈接服務(wù)器登錄映射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-為查詢結(jié)果添加一列序號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-監(jiān)控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創(chuàng)建及獲取表的描述，及字段描述信息-創(chuàng)建表描述COMMENT ON TABLE myTable is 表的描述信息-創(chuàng)建字段描述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創(chuàng)建及獲取表的描述，及字段描述信息-創(chuàng)建表描述EXEC sp_addextendedproperty 描述類別(可自定義), 表的描述信息, user, dbo, table, myTableName, null, null-創(chuàng)建字段描述EXEC sp_addextendedproperty 描述類別(可自定義), 字段的描述信息,