实用sql语句：查询结果导出到excel_收缩数据库

SqlServer

exec master..xp_cmdshell 'bcp \"select * from mydatabase.dbo.mytable\" queryout c:\emp.xls -c -q -S\".\" -U\"sa\" -P\"1\"'

--excel导入到SqlServer

BULK INSERT temp1 FROM 'c:\emp1.xls'

--收缩数据库

--首先截断事务日志

backup log mydatabase with no_log

--收缩数据库

dbcc shrinkdatabase('mydatabase',0)

--查SqlServer视图sql

select text from syscomments where id=object_id('reportsbaseview')

select * from information_schema.views

1 / 17

--查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:列描述

2 / 17

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 int

set @i=500

while (@i<600)

begin

insert into MYTABLE (invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus,projecttypeid)

values(@i,@i,@i,'100001',136,0,11)

set @i=@i+1

end

3 / 17

--查询每个表有几条记录

declare @colId varchar(50)

DECLARE detailCustom_Cursor CURSOR FOR

select 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_Cursor

FETCH NEXT FROM detailCustom_Cursor

into @colId

begin tran t1

declare @sql varchar(8000)

set @sql = ''

WHILE @@FETCH_STATUS = 0

4 / 17

BEGIN

if(len(@sql)<=7800)

begin

set @sql = @sql + ' select '''+@colId+''' as tableName,count(*) as data from '+@colId

set @sql = @sql + ' union all '

end

FETCH NEXT FROM detailCustom_Cursor

into @colId

END

set @sql = @sql + ' select ''-1'',-1'

exec(@sql)

commit tran t1

CLOSE detailCustom_Cursor

5 / 17

DEALLOCATE detailCustom_Cursor

C#与sql相关

1,取1条sql语句——除了某几列，其他列的都查出来，在aspx页面中写：

<%

string[] temp1 = new string[2] {\"InviteId\

string temp = GetExtraSql(\"tcontent\

Response.Write(temp);

%>

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);

6 / 17

if(temp!=null)

{

sql = \"select \";

DataTable tableObj = temp.Tables[0];

for(int i=0;i{

//如果找不到则添加

if(!StringInArray(exceptionColumns,tableObj.Columns[i].ToString()))

{

sql += tableObj.Columns[i]+\

}

}

int flag = sql.Length;

7 / 17

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;i{

if(arrayObj[i].ToUpper()==data.ToUpper())

{

8 / 17

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',1

RECONFIGURE WITH OVERRIDE

exec sp_configure 'Ad Hoc Distributed Queries',1

RECONFIGURE WITH OVERRIDE

9 / 17

--另一种方式：用链接服务器查询另外一个数据库服务器的表数据

--建立链接服务器

EXEC sp_addlinkedserver

'mycomputer',

'',

'MSDASQL',

NULL,

NULL,

'DRIVER={SQL Server};SERVER=192.168.0.203;UID=sa;PWD=1;'

GO

--建立链接服务器登录映射

exec sp_addlinkedsrvlogin

@rmtsrvname='mycomputer',@useself='false',@locallogin='Administrators',@rmtuser='sa',

10 / 17

@rmtpassword='1'

select * from mycomputer.mydatabase.dbo.users

--SqlServer批量改某一列的类型，temp1为零时表

declare @col1 varchar(50)

declare @col2 varchar(50)

DECLARE detailCustom_Cursor CURSOR FOR

select column_name col1,table_name col2 from information_schema.columns where data_type = 'decimal'

OPEN detailCustom_Cursor

FETCH NEXT FROM detailCustom_Cursor

into @col1,@col2

begin tran t1

WHILE @@FETCH_STATUS = 0

BEGIN

11 / 17

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

into @col1,@col2

END

commit tran t1

CLOSE detailCustom_Cursor

DEALLOCATE detailCustom_Cursor

--为查询结果添加一列序号

Select (select Count(*) from FIELDDICTIONARY FIELDDICTIONARYid<=FIELDDICTIONARY.FIELDDICTIONARYid) 12 / 17

T where T.

as Nbr ,*

From FIELDDICTIONARY order by nbr asc

13 / 17

--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_text

from v$session a,v$sess_io b,v$sqltext c

where a.sid=b.sid AND a.sql_address=c.address

AND a.username IS NOT NULL

order by a.username,c.sql_id,c.piece

--Oracle创建及获取表的描述，及字段描述信息

--创建表描述

COMMENT ON TABLE myTable is '表的描述信息'

14 / 17

--创建字段描述

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 '描述类别(可自定义)'', '字段的描述信息', 'user', dbo, 'table', myTableName, 'column', myColumnName

--获取表描述信息

15 / 17

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'myTableName' , null, default)

--获取字段描述信息

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'myTableName', 'column', default)

--修改oracle的dbf文件的位置

c:\\>sqlplus /nolog

SQL> conn / as sysdba;

SQL>shutdown immediate

SQL>startup mount;

SQL>SELECT FILE# ,ERROR FROM V$RECOVER_FILE;--查看是否有挂接失败数据文件

SQL>select r.file# ,D.name,r.error

from v$ercover_file r , v$datafile D

where r.file#=D.file#;

16 / 17

--查看失败文件的路径(如果没有挂接失败的数据文件，可以直接查v$datafile)

SQL>alter database rename file 'E:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS01.DBF' TO 'D:\\oracle\\product\\10.2.0\\oradata\\orcl\\USERS01.DBF';

--同上。。。。。

--改之

SQL> ALTER DATABASE OPEN;

17 / 17