实用参考资料sql语句：查询结果导出到excel-收缩数据库

1、-查询结果导出到excelSqlServerexecmaster.xp_cmdshellbcpselect*fromqueryoutc:temp.xls-c-q-S.-Usa-P1-excel 导入到 SqlServerBULKINSERTtemp1FROMc:temp1.xls-收缩数据库-首先截断事务日志backuplogmydatabase withno_log-收缩数据库dbccshrinkdatabase(mydatabase,0)-查 SqlServer 视图 sqlselect textfrom syscomments whereid=object_id(reportsbasev

2、iew)select *from information_schema.views-查 SqlServer:表名select *from information_schema.tables wheretable_name like%MYTABLE%-查 Oracle:表名select *from sys.all_tables where table_name = MYTABLE-查 Sqlserver 列名select *from information_schema.columnswhere table_name =MYTABLE-查 Orable:列名select *from sys.al

3、l_tab_cols wheretable_name = MYTABLE-查 Sqlserver 列描述SELECT*FROM:fn_listextendedproperty (NULL,user ,dbo,table,MYTABLE, column, default)-查 Orable: 列描述select *from sys.all_col_comments wheretable_name = MYTABLE-为查询结果添加序号（pkId 必须是整数类型）select number1=(selectcount(userId)fromtuserset as t2wheret2.pkId=t1

4、.pkId),userId,setName fromtuserset as t1-插入 100 条测试记录declareiintset i=500while (i600)begin1insert into MYTABLE(invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus,projecttypeid)values(i,i,i,100001,136,0,11)set i=i+1end-查询每个表有几条记录declare colIdvarchar(50)DECLAREdetailCustom_Curso

5、rCURSOR 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.tablesorder by table_name)OPEN detailCustom_CursorFETCH NEXTFROMd

6、etailCustom_Cursorinto colIdbegin tran t1declare sql varchar(8000)set sql = WHILEFETCH_STATUS= 0BEGINif(len(sql)=7800)beginset sql = sql + select +colId+as tableName,count(*)as data from+colIdset sql = sql + union all endFETCHNEXTFROMdetailCustom_Cursorinto colIdENDset sql = sql + select -1,-1exec(s

7、ql)commit tran t1CLOSE detailCustom_CursorDEALLOCATEdetailCustom_CursorC#与 sql 相关21,取 1 条 sql 语句除了某几列，其他列的都查出来，在aspx 页面中写：protected string GetExtraSql(stringtableName,stringexceptionColumns)string returnString = String.Empty;string sql = select top 1 * from+tableName;DataSet temp = ;if(temp!=null)sq

8、l = select ;DataTable tableObj = temp.Tables0;for(inti=0;i;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 StringInArr

9、ay(stringarrayObj,stringdata)bool returnValue = false;3for (int i=0;iarrayObj.Length;i+)if(arrayObji.ToUpper()=data.ToUpper()returnValue = true;break;return returnValue;-查询另外一个数据库服务器的表数据:SELECT * FROM OPENDA TASOURCE(SQLOLEDB,Data Source=;database=mydatabase;u ser id=sa;Password=1).-在 sqlserver2005

10、执行上面语句可能报权限错误，需要开启一下相关参数：exec sp_configureshow advanced options,1RECONFIGUREWITHOVERRIDEexec sp_configureAd Hoc DistributedQueries,1RECONFIGUREWITHOVERRIDE-另一种方式：用链接服务器查询另外一个数据库服务器的表数据-建立链接服务器EXECsp_addlinkedservermycomputer,MSDASQL,NULL,NULL,DRIVER=SQLServer;SERVER=;UID=sa;PWD=1;GO-建立链接服务器登录映射exec

11、sp_addlinkedsrvloginrmtsrvname=mycomputer,useself=false,locallogin=Administrators,rmtuser=sa,rmtpassword=1select *from4-SqlServer 批量改某一列的类型，temp1 为零时表declare col1 varchar(50)declare col2 varchar(50)DECLAREdetailCustom_CursorCURSOR FORselect column_name col1,table_name col2 from information_schema.co

12、lumns where data_typ e = decimalOPEN detailCustom_CursorFETCH NEXTFROMdetailCustom_Cursorinto col1,col2begin tran t1WHILEFETCH_STATUS= 0BEGINbegindeclare sql varchar(255)set sql = alter table +col2+ alter column +col1+ int null insert into temp1 values (sql)endFETCHNEXTFROMdetailCustom_Cursorinto co

13、l1,col2ENDcommit tran t1CLOSE detailCustom_CursorDEALLOCATEdetailCustom_Cursor-为查询结果添加一列序号Select(selectCount(*)fromFIELDDICTIONARYTwhereT.FIELDDICTIONARYidsqlplus/nologSQL conn / as sysdba;SQLshutdownimmediateSQLstartup mount;SQLSELECTFILE#,ERRORFROMV$RECOVER_FILE;-查看是否有挂接失败数据文件SQLselect r.file# ,D.