SQL 语法备忘

==========  原创作品    作者：未闻    出处：博客园  ==========

SQLServer

-- 判断表是否存在
IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'TableName') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

-- 查询XML字段中所有TableName节点的值
SELECT A.FID,scope.tbName.value('.[1]','varchar(200)') AS FTableName
FROM T_META_OBJECTTYPE A
CROSS APPLY
A.FKERNELXML.nodes('//TableName') AS scope(tbName) 

-- 使用游标遍历所有用户表
DECLARE @table_name VARCHAR(128)
-- 定义游标
DECLARE aCur CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE OBJECTPROPERTY(ID, N'IsUserTable') = 1 AND NAME NOT LIKE N'#%' ORDER BY NAME
-- 打开游标
OPEN aCur
FETCH NEXT FROM aCur INTO @table_name

WHILE @@FETCH_STATUS=0
BEGIN
    -- 打印表名
    PRINT(@table_name) 
    -- 读取下一条记录
    FETCH NEXT FROM aCur INTO @table_name
END
-- 关闭释放游标
CLOSE aCur
DEALLOCATE aCur

-- 使用WITH-AS遍历树节点及其所有子节点
WITH TEMP
AS
(
    SELECT * FROM TableName  WHERE FID='RootID'
    UNION ALL
    SELECT A.* FROM TableName A INNER JOIN TEMP B ON A.ParentID = B.FID
)SELECT * FROM TEMP