[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