스팅의 불(火)로그

테이블 명세 쿼리.sql

-- SQL Server 2005 이상에서사용할수있습니다.

-- DELETE문을사용하므로대용량테이블이존재한다면이스크립트는부적합할수있습니다.

-- Ctrl-Shift-M을눌러데이터베이스이름과삭제하지않을테이블을설정한후실행합니다.

USE <Database Name,sysname,>;

GO

SET NOCOUNT ON;

GO

DECLARE

    @nvcStmt nvarchar(max),

    @nvcIgnoreTables nvarchar(max);

DECLARE @tblTargetTables table (

    [object_id] int NOT NULL PRIMARY KEY,

    schemaName sysname NOT NULL,

    tableName sysname NOT NULL

);

DECLARE @tblIdentityTables table (

    tableName sysname NOT NULL,

    seed_value int NOT NULL,

    increment_value int NOT NULL,

    last_value int NULL

);

SET @nvcIgnoreTables = N'<삭제제외테이블을comma를구분자로나열,,>';

-- 삭제대상테이블정보를수집합니다.

INSERT @tblTargetTables ([object_id], schemaName, tableName)

SELECT T.[object_id], S.name, T.name

FROM sys.tables T

    INNER JOIN sys.schemas S ON T.[schema_id] = S.[schema_id]

WHERE T.type = 'U' AND NOT EXISTS (

    SELECT *

    FROM (

        SELECT LTRIM(RTRIM(SUBSTRING(

              N',' + @nvcIgnoreTables + N','

            , number + 1

            , CHARINDEX(N',', N',' + @nvcIgnoreTables + N',', number + 1) - number - 1

        ))) AS tableName

        FROM master.dbo.spt_values

        WHERE [type] = 'P' AND number < (DATALENGTH(N',' + @nvcIgnoreTables + N',') / 2)

            AND SUBSTRING(N',' + @nvcIgnoreTables + N',', number, 1) = N','

    ) S

    WHERE tableName = T.name

);

-- 삭제대상테이블의identity 속성컬럼정보를수집합니다.

INSERT @tblIdentityTables (tableName, seed_value, increment_value, last_value)

SELECT T.tableName, CAST(I.seed_value AS int), CAST(I.increment_value AS int)

    , CAST(I.last_value AS int)

FROM @tblTargetTables T

    INNER JOIN sys.identity_columns I ON T.[object_id] = I.[object_id];

-- CONSTRAINT와TRIGGER를비활성화하는구문생성

SET @nvcStmt = N'DISABLE TRIGGER ALL ON DATABASE;

EXEC sp_MSForEachTable ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'';

EXEC sp_MSForEachTable ''ALTER TABLE ? DISABLE TRIGGER ALL'';';

-- 테이블DELETE 구문생성

SELECT @nvcStmt = @nvcStmt + NCHAR(13) + NCHAR(10) +

    N'DELETE ' + QUOTENAME(schemaName) + N'.' + QUOTENAME(tableName) + N';'

FROM @tblTargetTables;

-- identity 속성의컬럼을초기화하는구문생성

SELECT @nvcStmt = @nvcStmt + NCHAR(13) + NCHAR(10) +

    N'DBCC CHECKIDENT(''' + tableName + ''', RESEED, ' +

    CASE

        WHEN last_value IS NULL THEN CAST(seed_value AS nvarchar(10))

        ELSE CAST(seed_value - increment_value AS nvarchar(10))

    END + N');'

FROM @tblIdentityTables;

SET @nvcStmt = @nvcStmt + '

EXEC sp_MSForEachTable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'';

EXEC sp_MSForEachTable ''ALTER TABLE ? ENABLE TRIGGER ALL'';

ENABLE TRIGGER ALL ON DATABASE;';

-- PRINT @nvcStmt;

EXEC sp_executesql @nvcStmt;



출처 : http://youngsam.kr/696

 목차

뷰, 파생 테이블 및 CTE
CTE의 구조
CTE의 이해
재귀의 규칙
간단한 재귀 연습
요약

 Figure 1 쿼리되는 뷰

코드 복사

CREATE VIEW vwMyView AS
SELECT
    EmployeeID, COUNT(*) AS NumOrders,  MAX(OrderDate) AS MaxDate
FROM Orders
GROUP BY EmployeeID
GO

SELECT 
    e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID, 
    om.NumOrders, om.MaxDate
FROM 
    Employees AS e
    INNER JOIN vwMyView AS oe ON e.EmployeeID = oe.EmployeeID
    INNER JOIN vwMyView AS om ON e.ReportsTo = om.EmployeeID

 Figure 2 파생 테이블을 사용하는 쿼리

코드 복사

SELECT 
    e.EmployeeID, oe.NumOrders, oe.MaxDate,  e.ReportsTo AS ManagerID, 
    om.NumOrders, om.MaxDate
FROM 
    Employees AS e
    INNER JOIN 
        (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
         FROM Orders
         GROUP BY EmployeeID) AS oe(EmployeeID, NumOrders, MaxDate)
        ON e.EmployeeID = oe.EmployeeID
    LEFT JOIN 
        (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
         FROM Orders
         GROUP BY EmployeeID) AS om(EmployeeID, NumOrders, MaxDate)
        ON e.ReportsTo = om.EmployeeID

 Figure 3 CTE를 사용한 쿼리

코드 복사

;WITH EmpOrdersCTE (EmployeeID, NumOrders, MaxDate) AS
(
  SELECT EmployeeID, COUNT(*), MAX(OrderDate)
  FROM Orders
  GROUP BY EmployeeID
)

SELECT 
    e.EmployeeID,  oe.NumOrders, oe.MaxDate,
    e.ReportsTo AS ManagerID,  om.NumOrders, om.MaxDate
FROM 
    Employees AS e
    INNER JOIN EmpOrdersCTE oe ON e.EmployeeID = oe.EmployeeID
    LEFT JOIN EmpOrdersCTE om ON e.ReportsTo = om.EmployeeID

;WITH myCTE (CustID, Co) AS
(
  SELECT CustomerID, CompanyName FROM Customers
)
SELECT CustID, Co FROM myCTE

;WITH myCTE (CustID, Co) AS
(
  SELECT CustomerID, CompanyName FROM Customers
)
SELECT CompanyName FROM Customers WHERE CustomerID = 'ALFKI'
SELECT CustID, Co FROM myCTE

;WITH myCTE AS
(
  SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate
  FROM Customers c 
  INNER JOIN Orders o ON c.CustomerID = o.CustomerID
)

SELECT CustomerID, CompanyName, OrderID, OrderDate 
FROM myCTE FOR XML AUTO

 Figure 4 다른 CTE를 참조하는 CTE

코드 복사

;WITH 
EmpOrdersCTE (EmployeeID, NumOrders)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Orders
  GROUP BY EmployeeID
),
MinMaxOrdersCTE (Mn, Mx, Diff)
AS
(
  SELECT MIN(NumOrders), MAX(NumOrders), AVG(NumOrders)
  FROM EmpOrdersCTE
)
SELECT Mn, Mx,    Diff
FROM MinMaxOrdersCTE

;WITH myRecursiveCTE(col1, col2, ... coln) AS
( 
  -- Anchor Member Query
  UNION ALL
  -- Recursive Member Query that references myRecursiveCTE
)

-- DEFINE YOUR CTE HERE
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 7)

 Figure 5 재귀적으로 판매 사원 정보 수집

코드 복사

;WITH EmpCTE(EmployeeID, EmployeeFirstName,
 EmployeeLastName, MgrID, SalesLevel)
AS
( 
  -- Anchor Member 
  SELECT EmployeeID, FirstName, LastName, ReportsTo, 0
  FROM Employees
  WHERE EmployeeID = 2 -- Start with the VP of Sales

  UNION ALL
  
  -- Recursive Member 
  SELECT 
    e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, m.SalesLevel+1
  FROM 
    Employees AS e
    INNER JOIN EmpCTE m ON e.ReportsTo = m.EmployeeID
)

 -- Using the CTE
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, 
       MgrID, SalesLevel  
FROM EmpCTE

John에게 질문이나 의견이 있으면 다음 전자 메일 주소로 보내시기 바랍니다: mmdata@microsoft.com.

해당 데이터베이스에 연결된 사용자가 있기 때문에 발생하는 오류

 실제 현재로 사용하고 있는 사용자가 있는 상태에서 데이터베이스 복원을 진행하기 위해서는

ALTER DATABASE 데이터베이스명
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

명령을 사용해서 모든 사용자에 대한 연결을 끊은 상태에서 진행하고

작업을 마친후

ALTER DATABASE DB명
SET MULTI_USER

로 되돌린다.

출처 : 네이버 지식인
http://kin.naver.com/qna/detail.nhn?d1id=1&dirId=10205&docId=122985369&qb=bXNzcWwgMjAwNSDrs7Xsm5A=&enc=utf8&section=kin&rank=7&search_sort=0&spq=0&sp=1&pid=gU300doi5TVssZz4RINsss--097953&sid=TX300UnZfU0AAEzIGnE