[tips]T_SQL @ 2000 AND 2005

if you can't create database diagrams on SQL server, execute the following T-SQL

USE   [ master ]
GO
EXEC  dbo.sp_dbcmptlevel  @dbname = ' CMDB ' ,  @new_cmptlevel = 90
GO

ALTER   AUTHORIZATION   ON   database ::dbname  TO  sa

Cursor

--  Declare a cursor
DECLARE  custom_cursor  CURSOR
 
FOR      SELECT  EmployeeID, LastName, FirstName  FROM  Employees

--  declare a temporary various
DECLARE   @tmopEmployeeID   INT
DECLARE   @LastName   varchar ( 50 )
DECLARE   @FirstName   varchar ( 50 )

--  open the cursor
OPEN  custom_cursor

-- GET THE NEXT RECORD
FETCH   NEXT   FROM  custom_cursor  INTO   @tmopEmployeeID  ,  @LastName ,  @FirstName

--  GET SUCCESSFULLY
WHILE   @@FETCH_STATUS   =   0

BEGIN  
     PRINT   CONVERT ( VARCHAR ( 20 ),  @tmopEmployeeID )  + ' ,  ' +   @LastName   +   ' . ' +   @FirstName
     FETCH   NEXT   FROM  custom_cursor  INTO   @tmopEmployeeID  ,  @LastName ,  @FirstName
END

-- DELETE THE CURSOR
DEALLOCATE  custom_cursor

 

-- 查询某一个库的所有存贮过程
select   *   from  sysobjects  where  type = ' P '      这是查出所有存储过程的名称等信息 
sp_helptext  [ 存储过程名 ]                             可以看到存储过程定义语句 
select   *   from  sysobjects  where  type = ' V '      这是查出所有视图的名称等信息 取出某个视图的生成该视图的SQL语句 

 

-- 链接服务器，用于不同种类或者服务器之间操作数据
Use  DBNAME
GO  
-- add link server--
Exec  sp_addlinkedserver  @server   =   ' linkedservername ' ,  @srvproduct = N ' SQL Server '  
GO  
--  list linked server--
Exec  sp_linkedservers 
Go  
-- delete data before insert
delete   from  tablename
GO  
--  insert data to the table
insert  tablename  select   distinct   *   from  linkedservername.DBNAME.dbo.tablename
GO  
--  delete the linked server--
Exec  sp_dropserver  ' hkhkpapt01 '  

when insert data, if you can't do successfully, pls note IDENTITY, change the column not as IDENTITY first, after finished to insert, change back

 

 

---处理sql for xml时的控制问题

当我们把查询结果作为xml输出的时候，如果有值为空值，应该怎么办呢？

 

 

Code
select * from tablename
for xml auto, elements xsinil

 

 

--pivot

 

Code
select * from
(
    select Currency, sum(cost) as CurCount 
    from dataTable
    group by Currency
) AS H
PIVOT 
( 
Sum(H.CurCount) for 
H.Currency in ([RMB],[USD])
)
AS C

 

 

如果还想获取更详细的信息，可以添加 type,或者xmlschema参数，获取更为详细的信息

 

 

 

---index: view the internal and external index fragmentation, and then rebuild or reorganize the index

 

Code
--Determine index fragmentation for all tables in the AdventureWorks database
SELECT OBJECT_NAME(dt.object_id), si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('DB_NAME'), NULL, NULL, NULL, 'DETAILED')
WHERE index_id <> 0) as dt --does not return information about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id


alter index index_name on table_name reorganize/rebuild

 

 

不使用游标更新数据表的思路是

1 移动当前表的数据到临时表

2 根据条件获取需要更新的数据

3 一个select，结合1和2，然后insert到目标表中去

ok

--------2009华丽分割线---- 

如果你更改多SQL server的名字，那么在有的时候，会出现14262的错误，比如在操作ship log的时候。

查看sql server的名字和现在你的服务器的名字是否一致

select * from sys.sysservers

如果不一致，执行下面两个存储过程，然后重启Windows

sp_dropserver 'servername'

go

sp_addserver 'servername'

 

ok, 问题搞定

 

对于上面这个问题的进一步更新

SELECT @@SERVERNAME

USE master
GO

--Drop old server name
EXEC sp_dropserver 'currentservername'
GO

--Add correct/ current server name
EXEC sp_addserver 'newservername', 'local'
GO

--restart the sql server service

SELECT @@SERVERNAME