SCHEMA Archives - 5 중 5 번째 페이지

[DATABASE/SQLSERVER] 테이블 리스트 구하기

■ 테이블 리스트를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_tables null,null,null, "'TABLE'";

EXECUTE sp_tables null,null,null, "'TABLE'";

[DATABASE/SQLSERVER] 뷰 리스트 구하기

■ 뷰 리스트를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_tables null, null, null, "'VIEW'";

EXECUTE sp_tables null, null, null, "'VIEW'";

[DATABASE/SQLSERVER] 테이블/뷰 리스트 구하기

■ 테이블/뷰 리스트를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_tables;

EXECUTE sp_tables;

[DATABASE/SQLSERVER] 데이터베이스 정보 구하기

■ 데이터베이스 정보를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_helpdb @dbname= '데이터베이스명';

EXECUTE sp_helpdb @dbname= '데이터베이스명';

[DATABASE/SQLSERVER] 데이터베이스 리스트 구하기

■ 데이터베이스 리스트를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_helpdb;

EXECUTE sp_helpdb;

[DATABASE/SQLSERVER] 테이블 존재 여부 조사하기

■ 테이블 존재 여부를 조사하는 방법을 보여준다. ▶ 테이블 (SQL)


SELECT COUNT(*) AS TableCount
FROM   SYSOBJECTS AS A
WHERE  A.xtype = 'U'
AND    A.name  = '테이블명';

SELECT COUNT(*) AS TableCount

FROM SYSOBJECTS AS A

WHERE A.xtype = 'U'

AND A.name = '테이블명';

▶ 임시 테이블 (SQL)


SELECT COUNT(*) AS TemporaryTableCount
FROM   tempdb.dbo.SYSOBJECTS AS A
WHERE  A.name LIKE '%테이블명%';

SELECT COUNT(*) AS TemporaryTableCount

FROM tempdb.dbo.SYSOBJECTS AS A

WHERE A.name LIKE '%테이블명%';

[DATABASE/SQLSERVER] 특정 테이블/컬럼을 FK로 참조하는 테이블 검색하기

■ 특정 테이블/컬럼을 FK로 참조하는 테이블을 검색하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT     D.name AS TableName
FROM       SYSOBJECTS     A
INNER JOIN SYSCOLUMNS     B ON  B.id     = A.id
INNER JOIN SYSFOREIGNKEYS C ON  C.rkeyid = A.id
                            AND C.rkey   = B.colid
INNER JOIN SYSOBJECTS     D ON  D.id     = C.fkeyid
WHERE A.name = 'Categories'  -- 테이블명
AND   B.name = 'CategoryID'; -- 컬럼명

SELECT D.name AS TableName

FROM SYSOBJECTS A

INNER JOIN SYSCOLUMNS B ON B.id = A.id

INNER JOIN SYSFOREIGNKEYS C ON C.rkeyid = A.id

AND C.rkey = B.colid

INNER JOIN SYSOBJECTS D ON D.id = C.fkeyid

WHERE A.name = 'Categories' -- 테이블명

AND B.name = 'CategoryID'; -- 컬럼명

[DATABASE/SQLSERVER] 특정 문자열을 사용하는 함수 및 저장 프로시저 검색하기

■ 특정 문자열을 사용하는 함수 및 저장 프로시저를 검색하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.[type] AS [Type]
   ,A.name   AS [Name]
   ,COUNT(1) AS [ReferencingCount]
FROM       SYSOBJECTS  A
INNER JOIN SYSCOMMENTS B ON B.id = A.id
WHERE A.type IN   ('FN', 'IF', 'TF', 'P')
AND   B.text LIKE '%CashBook%' -- 검색문자열
GROUP BY
    A.[type]
   ,A.name;

SELECT

A.[type] AS [Type]

,A.name AS [Name]

,COUNT(1) AS [ReferencingCount]

FROM SYSOBJECTS A

INNER JOIN SYSCOMMENTS B ON B.id = A.id

WHERE A.type IN ('FN', 'IF', 'TF', 'P')

AND B.text LIKE '%CashBook%' -- 검색문자열

GROUP BY

A.[type]

,A.name;

[DATABASE/SQLSERVER] 특정 테이블에서 NULL 값만 가진 컬럼 찾기

■ 특정 테이블에서 NULL 값만 가진 컬럼을 찾는 방법을 보여준다. ▶ 특정 테이블에서 NULL 값만 가진 컬럼 찾기 예제 (SQL)


EXECUTE GetEntireNullFieldList 'PhoneBook';

EXECUTE GetEntireNullFieldList 'PhoneBook';

▶

[DATABASE/SQLSERVER] 특정 문자열을 포함하는 저장 프로시저 조회하기

■ 특정 문자열을 포함하는 저장 프로시저를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DISTINCT
	B.name
FROM       syscomments A
INNER JOIN sysobjects  B ON B.id = A.id
WHERE A.text LIKE '%검색문자열%';

SELECT DISTINCT

B.name

FROM syscomments A

INNER JOIN sysobjects B ON B.id = A.id

WHERE A.text LIKE '%검색문자열%';

[DATABASE/SQLSERVER] 컬럼 설명 설정하기

■ 컬럼 설명을 설정하는 방법을 보여준다. ▶ 컬럼 설명 추가하기 (SQL)


EXECUTE sys.SP_ADDEXTENDEDPROPERTY
    @name=N'MS_Description',
    @value=N'메뉴ID'       , -- 컬럼설명
    @level0type=N'SCHEMA'  ,
    @level0name=N'dbo'     ,
    @level1type=N'TABLE'   ,
    @level1name=N'CashBook', -- 테이블명
    @level2type=N'COLUMN'  ,
    @level2name=N'MenuID'    -- 컬럼명
GO

EXECUTE sys.SP_ADDEXTENDEDPROPERTY

@name=N'MS_Description',

@value=N'메뉴ID' , -- 컬럼설명

@level0type=N'SCHEMA' ,

@level0name=N'dbo' ,

@level1type=N'TABLE' ,

@level1name=N'CashBook', -- 테이블명

@level2type=N'COLUMN' ,

@level2name=N'MenuID' -- 컬럼명

▶ 컬럼 설명 갱신하기 (SQL)


EXECUTE sys.SP_UPDATEEXTENDEDPROPERTY
    @name=N'MS_Description',
    @value=N'메뉴ID'       , -- 컬럼설명
    @level0type=N'SCHEMA'  ,
    @level0name=N'dbo'     ,
    @level1type=N'TABLE'   ,
    @level1name=N'CashBook', -- 테이블명
    @level2type=N'COLUMN'  ,
    @level2name=N'MenuID'    -- 컬럼명
GO

EXECUTE sys.SP_UPDATEEXTENDEDPROPERTY

@name=N'MS_Description',

@value=N'메뉴ID' , -- 컬럼설명

@level0type=N'SCHEMA' ,

@level0name=N'dbo' ,

@level1type=N'TABLE' ,

@level1name=N'CashBook', -- 테이블명

@level2type=N'COLUMN' ,

@level2name=N'MenuID' -- 컬럼명

[DATABASE/SQLSERVER] 테이블 전체 행 수 구하기

■ 테이블의 전체 행 수를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT A.[rows] AS [RowCount]
FROM   sysindexes AS A
WHERE  A.id = OBJECT_ID('PhotoBook')
AND    A.indid < 2;

SELECT A.[rows] AS [RowCount]

FROM sysindexes AS A

WHERE A.id = OBJECT_ID('PhotoBook')

AND A.indid < 2;

※ 테이블명 : PhotoBook

[DATABASE/SQLSERVER] 저장 프로시저 정보 구하기

■ 저장 프로시저 정보를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_help [저장 프로시저명];

EXECUTE sp_help [저장 프로시저명];

[DATABASE/SQLSERVER] 테이블 정보 구하기

■ 테이블 정보를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_help [테이블명];

EXECUTE sp_help [테이블명];

[DATABASE/SQLSERVER] 데이터베이스 제약 조건 컬럼 조회하기

■ 데이터베이스 제약 조건 컬럼을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.CONSTRAINT_CATALOG                  AS ConstraintDatabase
   ,A.CONSTRAINT_SCHEMA                   AS ConstraintSchema
   ,A.CONSTRAINT_NAME                     AS [Constraint]
   ,A.TABLE_CATALOG                       AS [Database]
   ,A.TABLE_SCHEMA                        AS [Schema]
   ,A.TABLE_NAME                          AS [Table]
   ,CONVERT(INT, B.ORDINAL_POSITION)      AS Sequence
   ,A.COLUMN_NAME                         AS [Column]
   ,UPPER(B.DATA_TYPE)                    AS DataType
   ,ISNULL(B.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize
   ,ISNULL(B.CHARACTER_OCTET_LENGTH, 0)   AS ByteSize
   ,ISNULL(B.NUMERIC_PRECISION, 0)        AS NumericPrecision
   ,ISNULL(B.NUMERIC_PRECISION_RADIX, 0)  AS NumericPrecisionRadix
   ,ISNULL(B.NUMERIC_SCALE, 0)            AS NumericScale
   ,ISNULL(B.DATETIME_PRECISION, 0)       AS DateTimePrecision
   ,CASE B.IS_NULLABLE
        WHEN 'YES' THEN 'NULL'
        ELSE            ''
    END                                   AS [NULL]
   ,(
        SELECT
            CASE
                WHEN COUNT(*) > 0 THEN 'Y'
                ELSE                   'N'
            END
        FROM  syscolumns AS C
        JOIN  sysobjects AS D ON D.id = C.id
        WHERE C.status = 128
        AND   D.xtype  = 'U'
        AND   D.name   = B.TABLE_NAME
        AND   C.name   = B.COLUMN_NAME
    )                                     AS IsIdentity
   ,ISNULL(B.COLUMN_DEFAULT, '')          AS [Default]
   ,ISNULL(B.CHARACTER_SET_CATALOG, '')   AS CharacterSetCatalog
   ,ISNULL(B.CHARACTER_SET_SCHEMA, '')    AS CharacterSetSchema
   ,ISNULL(B.CHARACTER_SET_NAME, '')      AS CharacterSetName
   ,ISNULL(B.COLLATION_CATALOG, '')       AS CollationCatalog
   ,ISNULL(B.COLLATION_NAME, '')          AS [CollationName]
   ,ISNULL(B.DOMAIN_CATALOG, '')          AS DomainCatalog
   ,ISNULL(B.DOMAIN_SCHEMA, '')           AS DomainSchema
   ,ISNULL(B.DOMAIN_NAME, '')             AS DomainName
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS A
JOIN INFORMATION_SCHEMA.COLUMNS                 AS B ON  A.TABLE_NAME  = B.TABLE_NAME
                                                     AND A.COLUMN_NAME = B.COLUMN_NAME
ORDER BY
    A.TABLE_NAME       ASC
   ,A.CONSTRAINT_NAME  ASC
   ,B.ORDINAL_POSITION ASC;

SELECT

A.CONSTRAINT_CATALOG AS ConstraintDatabase

,A.CONSTRAINT_SCHEMA AS ConstraintSchema

,A.CONSTRAINT_NAME AS [Constraint]

,A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,CONVERT(INT, B.ORDINAL_POSITION) AS Sequence

,A.COLUMN_NAME AS [Column]

,UPPER(B.DATA_TYPE) AS DataType

,ISNULL(B.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize

,ISNULL(B.CHARACTER_OCTET_LENGTH, 0) AS ByteSize

,ISNULL(B.NUMERIC_PRECISION, 0) AS NumericPrecision

,ISNULL(B.NUMERIC_PRECISION_RADIX, 0) AS NumericPrecisionRadix

,ISNULL(B.NUMERIC_SCALE, 0) AS NumericScale

,ISNULL(B.DATETIME_PRECISION, 0) AS DateTimePrecision

,CASE B.IS_NULLABLE

WHEN 'YES' THEN 'NULL'

ELSE ''

END AS [NULL]

SELECT

CASE

WHEN COUNT(*) > 0 THEN 'Y'

ELSE 'N'

END

FROM syscolumns AS C

JOIN sysobjects AS D ON D.id = C.id

WHERE C.status = 128

AND D.xtype = 'U'

AND D.name = B.TABLE_NAME

AND C.name = B.COLUMN_NAME

) AS IsIdentity

,ISNULL(B.COLUMN_DEFAULT, '') AS [Default]

,ISNULL(B.CHARACTER_SET_CATALOG, '') AS CharacterSetCatalog

,ISNULL(B.CHARACTER_SET_SCHEMA, '') AS CharacterSetSchema

,ISNULL(B.CHARACTER_SET_NAME, '') AS CharacterSetName

,ISNULL(B.COLLATION_CATALOG, '') AS CollationCatalog

,ISNULL(B.COLLATION_NAME, '') AS [CollationName]

,ISNULL(B.DOMAIN_CATALOG, '') AS DomainCatalog

,ISNULL(B.DOMAIN_SCHEMA, '') AS DomainSchema

,ISNULL(B.DOMAIN_NAME, '') AS DomainName

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS A

JOIN INFORMATION_SCHEMA.COLUMNS AS B ON A.TABLE_NAME = B.TABLE_NAME

AND A.COLUMN_NAME = B.COLUMN_NAME

ORDER BY

A.TABLE_NAME ASC

,A.CONSTRAINT_NAME ASC

,B.ORDINAL_POSITION ASC;

[DATABASE/SQLSERVER] 데이터베이스 제약 조건 조회하기

■ 데이터베이스 제약 조건을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG      AS [Database]
   ,A.TABLE_SCHEMA       AS [Schema]
   ,A.TABLE_NAME         AS [Table]
   ,A.CONSTRAINT_CATALOG AS ConstraintDatabase
   ,A.CONSTRAINT_SCHEMA  AS ConstraintSchema
   ,A.CONSTRAINT_NAME    AS [Constraint]
   ,CASE A.CONSTRAINT_TYPE
        WHEN 'PRIMARY KEY' THEN 'PK'
        WHEN 'FOREIGN KEY' THEN 'FK'
        WHEN 'CHECK'       THEN 'CK'
        WHEN 'UNIQUE'      THEN 'UK'
        ELSE                    ''
    END                  AS ConstraintType
   ,CASE A.IS_DEFERRABLE
        WHEN 'YES' THEN 'Y'
        WHEN 'NO'  THEN 'N'
        ELSE            ''
    END                  AS IsDeferrable
   ,CASE A.INITIALLY_DEFERRED
        WHEN 'YES' THEN 'Y'
        WHEN 'NO'  THEN 'N'
        ELSE            ''
    END                 AS InitiallyDeferred
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS A
WHERE A.TABLE_NAME <> 'sysdiagrams'
ORDER BY
    A.TABLE_NAME      ASC
   ,A.CONSTRAINT_TYPE DESC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,A.CONSTRAINT_CATALOG AS ConstraintDatabase

,A.CONSTRAINT_SCHEMA AS ConstraintSchema

,A.CONSTRAINT_NAME AS [Constraint]

,CASE A.CONSTRAINT_TYPE

WHEN 'PRIMARY KEY' THEN 'PK'

WHEN 'FOREIGN KEY' THEN 'FK'

WHEN 'CHECK' THEN 'CK'

WHEN 'UNIQUE' THEN 'UK'

ELSE ''

END AS ConstraintType

,CASE A.IS_DEFERRABLE

WHEN 'YES' THEN 'Y'

WHEN 'NO' THEN 'N'

ELSE ''

END AS IsDeferrable

,CASE A.INITIALLY_DEFERRED

WHEN 'YES' THEN 'Y'

WHEN 'NO' THEN 'N'

ELSE ''

END AS InitiallyDeferred

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS A

WHERE A.TABLE_NAME <> 'sysdiagrams'

ORDER BY

A.TABLE_NAME ASC

,A.CONSTRAINT_TYPE DESC;

[DATABASE/SQLSERVER] 데이터베이스 뷰 컬럼 조회하기

■ 데이터베이스 뷰 컬럼을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG                       AS [Database]
   ,A.TABLE_SCHEMA                        AS [Schema]
   ,A.TABLE_NAME                          AS [Table]
   ,CONVERT(INT, A.ORDINAL_POSITION)      AS Sequence
   ,A.COLUMN_NAME                         AS [Column]
   ,UPPER(A.DATA_TYPE)                    AS DataType
   ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize
   ,ISNULL(A.CHARACTER_OCTET_LENGTH, 0)   AS ByteSize
   ,ISNULL(A.NUMERIC_PRECISION, 0)        AS NumericPrecision
   ,ISNULL(A.NUMERIC_PRECISION_RADIX, 0)  AS NumericPrecisionRadix
   ,ISNULL(A.NUMERIC_SCALE, 0)            AS NumericScale
   ,ISNULL(A.DATETIME_PRECISION, 0)       AS DateTimePrecision
   ,CASE A.IS_NULLABLE
        WHEN 'YES' THEN 'NULL'
        ELSE            ''
    END                                   AS [NULL]
   ,(
        SELECT
            CASE
                WHEN COUNT(*) > 0 THEN 'Y'
                ELSE                   'N'
            END
        FROM  syscolumns AS B
        JOIN  sysobjects AS C ON C.id = B.id
        WHERE B.status = 128
        AND   C.xtype  = 'U'
        AND   C.name   = A.TABLE_NAME
        AND   B.name   = A.COLUMN_NAME
    )                                     AS IsIdentity
   ,ISNULL(A.DOMAIN_NAME, '')             AS DomainName
   ,ISNULL(A.COLUMN_DEFAULT, '')          AS [Default]
   ,ISNULL(A.CHARACTER_SET_CATALOG, '')   AS CharacterSetCatalog
   ,ISNULL(A.CHARACTER_SET_SCHEMA, '')    AS CharacterSetSchema
   ,ISNULL(A.CHARACTER_SET_NAME, '')      AS CharacterSetName
   ,ISNULL(A.COLLATION_CATALOG, '')       AS CollationCatalog
   ,ISNULL(A.COLLATION_NAME, '')          AS [CollationName]
   ,ISNULL(A.DOMAIN_CATALOG, '')          AS DomainCatalog
   ,ISNULL(A.DOMAIN_SCHEMA, '')           AS DomainSchema
FROM  INFORMATION_SCHEMA.COLUMNS AS A
WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW')
ORDER BY
    A.TABLE_NAME       ASC
   ,A.ORDINAL_POSITION ASC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,CONVERT(INT, A.ORDINAL_POSITION) AS Sequence

,A.COLUMN_NAME AS [Column]

,UPPER(A.DATA_TYPE) AS DataType

,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize

,ISNULL(A.CHARACTER_OCTET_LENGTH, 0) AS ByteSize

,ISNULL(A.NUMERIC_PRECISION, 0) AS NumericPrecision

,ISNULL(A.NUMERIC_PRECISION_RADIX, 0) AS NumericPrecisionRadix

,ISNULL(A.NUMERIC_SCALE, 0) AS NumericScale

,ISNULL(A.DATETIME_PRECISION, 0) AS DateTimePrecision

,CASE A.IS_NULLABLE

WHEN 'YES' THEN 'NULL'

ELSE ''

END AS [NULL]

SELECT

CASE

WHEN COUNT(*) > 0 THEN 'Y'

ELSE 'N'

END

FROM syscolumns AS B

JOIN sysobjects AS C ON C.id = B.id

WHERE B.status = 128

AND C.xtype = 'U'

AND C.name = A.TABLE_NAME

AND B.name = A.COLUMN_NAME

) AS IsIdentity

,ISNULL(A.DOMAIN_NAME, '') AS DomainName

,ISNULL(A.COLUMN_DEFAULT, '') AS [Default]

,ISNULL(A.CHARACTER_SET_CATALOG, '') AS CharacterSetCatalog

,ISNULL(A.CHARACTER_SET_SCHEMA, '') AS CharacterSetSchema

,ISNULL(A.CHARACTER_SET_NAME, '') AS CharacterSetName

,ISNULL(A.COLLATION_CATALOG, '') AS CollationCatalog

,ISNULL(A.COLLATION_NAME, '') AS [CollationName]

,ISNULL(A.DOMAIN_CATALOG, '') AS DomainCatalog

,ISNULL(A.DOMAIN_SCHEMA, '') AS DomainSchema

FROM INFORMATION_SCHEMA.COLUMNS AS A

WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW')

ORDER BY

A.TABLE_NAME ASC

,A.ORDINAL_POSITION ASC;

[DATABASE/SQLSERVER] 데이터베이스 뷰 조회하기

■ 데이터베이스 뷰를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG AS [Database]
   ,A.TABLE_SCHEMA  AS [Schema]
   ,A.TABLE_NAME    AS [Table]
FROM     INFORMATION_SCHEMA.TABLES AS A
WHERE    A.TABLE_TYPE = 'VIEW'
ORDER BY A.TABLE_NAME ASC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

FROM INFORMATION_SCHEMA.TABLES AS A

WHERE A.TABLE_TYPE = 'VIEW'

ORDER BY A.TABLE_NAME ASC;

[DATABASE/SQLSERVER] 데이타베이스 테이블 조회하기

■ 데이타베이스 테이블을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG AS [Database]
   ,A.TABLE_SCHEMA  AS [Schema]
   ,A.TABLE_NAME    AS [Table]
FROM     INFORMATION_SCHEMA.TABLES AS A
WHERE    A.TABLE_TYPE = 'BASE TABLE'
ORDER BY A.TABLE_NAME;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

FROM INFORMATION_SCHEMA.TABLES AS A

WHERE A.TABLE_TYPE = 'BASE TABLE'

ORDER BY A.TABLE_NAME;

[DATABASE/SQLSERVER] 데이타베이스 테이블 컬럼 조회하기

■ 데이타베이스 테이블 컬럼을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG                       AS [Database]
   ,A.TABLE_SCHEMA                        AS [Schema]
   ,A.TABLE_NAME                          AS [Table]
   ,CONVERT(INT, A.ORDINAL_POSITION)      AS Sequence
   ,A.COLUMN_NAME                         AS [Column]
   ,UPPER(A.DATA_TYPE)                    AS DataType
   ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize
   ,ISNULL(A.CHARACTER_OCTET_LENGTH, 0)   AS ByteSize
   ,ISNULL(A.NUMERIC_PRECISION, 0)        AS NumericPrecision
   ,ISNULL(A.NUMERIC_PRECISION_RADIX, 0)  AS NumericPrecisionRadix
   ,ISNULL(A.NUMERIC_SCALE, 0)            AS NumericScale
   ,ISNULL(A.DATETIME_PRECISION, 0)       AS DateTimePrecision
   ,CASE A.IS_NULLABLE
        WHEN 'YES' THEN 'NULL'
        ELSE            ''
    END                                   AS [NULL]
   ,(
        SELECT
            CASE
                WHEN COUNT(*) > 0 THEN 'Y'
                ELSE                   'N'
            END
        FROM  syscolumns AS B
        JOIN  sysobjects AS C ON C.id = B.id
        WHERE B.status = 128
        AND   C.xtype  = 'U'
        AND   C.name   = A.TABLE_NAME
        AND   B.name   = A.COLUMN_NAME
    )                                     AS IsIdentity
   ,ISNULL(A.COLUMN_DEFAULT, '')          AS [Default]
   ,ISNULL(A.CHARACTER_SET_CATALOG, '')   AS CharacterSetCatalog
   ,ISNULL(A.CHARACTER_SET_SCHEMA, '')    AS CharacterSetSchema
   ,ISNULL(A.CHARACTER_SET_NAME, '')      AS CharacterSetName
   ,ISNULL(A.COLLATION_CATALOG, '')       AS CollationCatalog
   ,ISNULL(A.COLLATION_NAME, '')          AS [CollationName]
   ,ISNULL(A.DOMAIN_CATALOG, '')          AS DomainCatalog
   ,ISNULL(A.DOMAIN_SCHEMA, '')           AS DomainSchema
   ,ISNULL(A.DOMAIN_NAME, '')             AS DomainName
FROM  INFORMATION_SCHEMA.COLUMNS AS A
WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')
ORDER BY
    A.TABLE_NAME       ASC
   ,A.ORDINAL_POSITION ASC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,CONVERT(INT, A.ORDINAL_POSITION) AS Sequence

,A.COLUMN_NAME AS [Column]

,UPPER(A.DATA_TYPE) AS DataType

,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize

,ISNULL(A.CHARACTER_OCTET_LENGTH, 0) AS ByteSize

,ISNULL(A.NUMERIC_PRECISION, 0) AS NumericPrecision

,ISNULL(A.NUMERIC_PRECISION_RADIX, 0) AS NumericPrecisionRadix

,ISNULL(A.NUMERIC_SCALE, 0) AS NumericScale

,ISNULL(A.DATETIME_PRECISION, 0) AS DateTimePrecision

,CASE A.IS_NULLABLE

WHEN 'YES' THEN 'NULL'

ELSE ''

END AS [NULL]

SELECT

CASE

WHEN COUNT(*) > 0 THEN 'Y'

ELSE 'N'

END

FROM syscolumns AS B

JOIN sysobjects AS C ON C.id = B.id

WHERE B.status = 128

AND C.xtype = 'U'

AND C.name = A.TABLE_NAME

AND B.name = A.COLUMN_NAME

) AS IsIdentity

,ISNULL(A.COLUMN_DEFAULT, '') AS [Default]

,ISNULL(A.CHARACTER_SET_CATALOG, '') AS CharacterSetCatalog

,ISNULL(A.CHARACTER_SET_SCHEMA, '') AS CharacterSetSchema

,ISNULL(A.CHARACTER_SET_NAME, '') AS CharacterSetName

,ISNULL(A.COLLATION_CATALOG, '') AS CollationCatalog

,ISNULL(A.COLLATION_NAME, '') AS [CollationName]

,ISNULL(A.DOMAIN_CATALOG, '') AS DomainCatalog

,ISNULL(A.DOMAIN_SCHEMA, '') AS DomainSchema

,ISNULL(A.DOMAIN_NAME, '') AS DomainName

FROM INFORMATION_SCHEMA.COLUMNS AS A

WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

ORDER BY

A.TABLE_NAME ASC

,A.ORDINAL_POSITION ASC;

[DATABASE/SQLSERVER] 데이터베이스명 구하기

■ 데이터베이스명을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT @@Servername AS DatabaseName;

/*
DatabaseName
------------
VOYAGER
*/

SELECT @@Servername AS DatabaseName;

DatabaseName

------------

VOYAGER

[DATABASE/SQLSERVER] 테이블명과 행 수 표시하기

■ 테이블명과 행 수를 표시하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.name   AS [TableName]
   ,B.[rows] AS [RowCount]
FROM SYSOBJECTS A
JOIN SYSINDEXES B ON B.id = A.id
WHERE A.xtype ='u'
AND   B.indid < 2
ORDER BY A.name ASC;

SELECT

A.name AS [TableName]

,B.[rows] AS [RowCount]

FROM SYSOBJECTS A

JOIN SYSINDEXES B ON B.id = A.id

WHERE A.xtype ='u'

AND B.indid < 2

ORDER BY A.name ASC;

[DATABASE/SQLSERVER] 데이터베이스 스키마 조회하기

■ 데이터베이스 스키마를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    UPPER(A.NAME)      AS [테이블영문명]
   ,B.COLORDER         AS [컬럼순번]
   ,UPPER(B.NAME)      AS [컬럼영문명]
   ,UPPER(C.NAME)      AS [자료형]
   ,B.LENGTH           AS [길이]
   ,ISNULL(B.SCALE, 0) AS [정밀도]
   ,ISNULL
    (
        'PK' +
        CONVERT
        (
            VARCHAR(10),
            (
                SELECT SC.KEYNO
                FROM       SYSINDEXES   AS SA WITH(NOLOCK)
                INNER JOIN SYSOBJECTS   AS SB WITH(NOLOCK) ON  SB.ID    =  SA.ID
                                                           AND SB.XTYPE =  'U'
                                                           AND SB.ID    =  A.ID
                INNER JOIN SYSINDEXKEYS AS SC WITH(NOLOCK) ON  SC.ID    =  SA.ID
                                                           AND SC.INDID =  SA.INDID
                INNER JOIN SYSCOLUMNS   AS SD WITH(NOLOCK) ON  SD.ID    =  SC.ID
                                                           AND SD.COLID =  SC.COLID
                                                           AND SD.COLID =  B.COLID
                                                           AND SD.ID    =  B.ID
                WHERE SA.INDID = 1
            )
        ),
        ''
    )                  AS [PK]
   ,(
        CASE B.ISNULLABLE
            WHEN 0 THEN ''
            WHEN 1 THEN 'NULL'
        END
    )                  AS [NULL]
   ,ISNULL(E.TEXT, '') AS [DEFAULT]
FROM      SYSOBJECTS     AS A WITH(NOLOCK)
LEFT JOIN SYSCOLUMNS     AS B WITH(NOLOCK) ON  B.ID    =  A.ID
LEFT JOIN SYSTYPES       AS C WITH(NOLOCK) ON  C.XTYPE =  B.XTYPE
                                           AND C.NAME  <> 'SYSNAME'
LEFT JOIN SYSCONSTRAINTS AS D WITH(NOLOCK) ON  D.ID    =  A.ID
                                           AND D.COLID =  B.COLID
LEFT JOIN SYSCOMMENTS    AS E WITH(NOLOCK) ON  E.ID    =  D.CONSTID
WHERE A.XTYPE = 'U'
ORDER BY A.NAME     ASC,
         B.COLORDER ASC,
         B.NAME     ASC;

SELECT

UPPER(A.NAME) AS [테이블영문명]

,B.COLORDER AS [컬럼순번]

,UPPER(B.NAME) AS [컬럼영문명]

,UPPER(C.NAME) AS [자료형]

,B.LENGTH AS [길이]

,ISNULL(B.SCALE, 0) AS [정밀도]

,ISNULL

(

'PK' +

CONVERT

(

VARCHAR(10),

(

SELECT SC.KEYNO

FROM SYSINDEXES AS SA WITH(NOLOCK)

INNER JOIN SYSOBJECTS AS SB WITH(NOLOCK) ON SB.ID = SA.ID

AND SB.XTYPE = 'U'

AND SB.ID = A.ID

INNER JOIN SYSINDEXKEYS AS SC WITH(NOLOCK) ON SC.ID = SA.ID

AND SC.INDID = SA.INDID

INNER JOIN SYSCOLUMNS AS SD WITH(NOLOCK) ON SD.ID = SC.ID

AND SD.COLID = SC.COLID

AND SD.COLID = B.COLID

AND SD.ID = B.ID

WHERE SA.INDID = 1

)

) AS [PK]

CASE B.ISNULLABLE

WHEN 0 THEN ''

WHEN 1 THEN 'NULL'

END

) AS [NULL]

,ISNULL(E.TEXT, '') AS [DEFAULT]

FROM SYSOBJECTS AS A WITH(NOLOCK)

LEFT JOIN SYSCOLUMNS AS B WITH(NOLOCK) ON B.ID = A.ID

LEFT JOIN SYSTYPES AS C WITH(NOLOCK) ON C.XTYPE = B.XTYPE

AND C.NAME <> 'SYSNAME'

LEFT JOIN SYSCONSTRAINTS AS D WITH(NOLOCK) ON D.ID = A.ID

AND D.COLID = B.COLID

LEFT JOIN SYSCOMMENTS AS E WITH(NOLOCK) ON E.ID = D.CONSTID

WHERE A.XTYPE = 'U'

ORDER BY A.NAME ASC,

B.COLORDER ASC,

B.NAME ASC;

[DATABASE/SQLSERVER] 컬럼 정보 조회하기

■ 컬럼 정보를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT A.*
FROM   INFORMATION_SCHEMA.COLUMNS AS A WITH(NOLOCK);

SELECT A.*

FROM INFORMATION_SCHEMA.COLUMNS AS A WITH(NOLOCK);