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

--查询结果导出到 excel SqlServer exec master..xp_cmdshell bcp select * from mydatabase.dbo.mytable quer yout c:\temp.xls -c -q -S. -Usa -P1 --excel 导入到 SqlServer BULK INSERT temp1 FROM c:\temp1.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 --查 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, def ault) --查 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,setN ame from tuserset as t1 --插入 100 条测试记录 declare @i int set @i=500 while (@i600) begin insert into MYTABLE (invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus, projecttypeid) values(@i,@i,@i,100001,136,0,11) set @i=@i+1 end --查询每个表有几条记录 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