实用sql语句查询结果导出到excel收缩数据库之欧阳家百创编.docx

1、实用sql语句查询结果导出到excel收缩数据库之欧阳家百创编-查询结果导出到excel欧阳家百（2021.03.07）SqlServerexec 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 shrinkdatabas

2、e(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列名sel

3、ect * 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 =

4、 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,projecttype

5、id)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

6、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 = 0BEGIN if(len(sql)=7800) begin set sql = sql + select +colId+ as tableName,count(*) as data f

7、rom +colId set sql = sql + union all end FETCH NEXT FROM detailCustom_Cursor into 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 exceptio

8、nColumns) 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,tableOb

9、j.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.

10、ToUpper()=data.ToUpper() returnValue = true; break; return returnValue; -查询另外一个数据库服务器的表数据:SELECT *FROM OPENDATASOURCE(SQLOLEDB,Data Source=192.168.0.12;database=mydatabase;user id=sa;Password=1).mydatabase.dbo.mytable-在sqlserver2005执行上面语句可能报权限错误，需要开启一下相关参数：exec sp_configure show advanced options,1RE

11、CONFIGURE WITH OVERRIDEexec sp_configure Ad Hoc Distributed Queries,1RECONFIGURE WITH OVERRIDE-另一种方式：用链接服务器查询另外一个数据库服务器的表数据-建立链接服务器EXEC sp_addlinkedserver mycomputer, , MSDASQL, NULL, NULL, DRIVER=SQL Server;SERVER=192.168.0.203;UID=sa;PWD=1;GO-建立链接服务器登录映射exec sp_addlinkedsrvloginrmtsrvname=mycomput

12、er,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 w

13、here data_type = decimal OPEN detailCustom_CursorFETCH NEXT FROM detailCustom_Cursorinto col1,col2begin tran t1WHILE FETCH_STATUS = 0BEGIN begin declare sql varchar(255) set sql = alter table +col2+ alter column +col1+ int null insert into temp1 values (sql) end FETCH NEXT FROM detailCustom_Cursor i

14、nto col1,col2ENDcommit tran t1CLOSE detailCustom_CursorDEALLOCATE detailCustom_Cursor-为查询结果添加一列序号Select (select Count(*) from FIELDDICTIONARY T where T.FIELDDICTIONARYidsqlplus /nolog SQL conn / as sysdba; SQLshutdown immediate SQLstartup mount; SQLSELECT FILE# ,ERROR FROM V$RECOVER_FILE;-查看是否有挂接失败数据文件 SQLselect r.file# ,D.name,r.error from v$ercover_file r , v$datafile D where r.file#=D.file#; -查看失败文件的路径(如果没有挂接失败的数据文件，可以直接查v$datafile) SQLalter database rename file E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF TO D:oracleproduct10.2.0oradataorclUSERS01.DBF; -同上。 -改之 SQL ALTER DATABASE OPEN;