시스템의 모든 테이블 정보

시스템의 모든 테이블 정보

if exists (select * from sysobjects where id = object_id(N'[dbo].[conv_tables_fields]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[conv_tables_fields]
GO

CREATE TABLE [dbo].[conv_tables_fields] (
 [TABLE_QUALIFIER] [nvarchar] (128) NULL ,
 [TABLE_OWNER] [nvarchar] (128) NULL ,
 [TABLE_NAME] [nvarchar] (128) NULL ,
 [COLUMN_NAME] [nvarchar] (128) NULL ,
 [DATA_TYPE] [smallint] NULL ,
 [TYPE_NAME] [nvarchar] (128) NULL ,
 [PRECISION] [int] NULL ,
 [LENGTH] [int] NULL ,
 [SCALE] [smallint] NULL ,
 [RADIX] [smallint] NULL ,
 [NULLABLE] [smallint] NULL ,
 [REMARKS] [varchar] (254) NULL ,
 [COLUMN_DEF] [nvarchar] (400) NULL ,
 [SQL_DATA_TYPE] [smallint] NULL ,
 [SQL_DATETIME_SUB] [smallint] NULL ,
 [CHAR_OCTET_LENGTH] [int] NULL ,
 [ORDINAL_POSITION] [int] NULL ,
 [IS_NULLABLE] [varchar] (254) NULL ,
 [SS_DATA_TYPE] [tinyint] NULL
) ON [PRIMARY]
GO

DECLARE @name nvarchar(384)
DECLARE @Dfile_header nvarchar(384)
DECLARE name_cursor CURSOR FOR Select name from sysobjects
where type = 'u'
order by name
OPEN name_cursor
FETCH NEXT FROM name_cursor INTO @name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 
 IF (@@FETCH_STATUS <> -2)
 BEGIN
  PRINT ' '
  SELECT @dfile_header =  RTRIM(@name) 
  PRINT @dfile_header
  PRINT ' Trans Begin'

  Begin transaction
  insert conv_tables_fields
  exec sp_columns @dfile_header
  commit transaction 
  PRINT ' Trans End'
 END
 FETCH NEXT FROM name_cursor INTO @name
END
PRINT ' '
PRINT ' '
SELECT @dfile_header = '*************  NO MORE TABLES'
   + '  *************'
PRINT @dfile_header
PRINT ' '
PRINT 'Shown All Files.'
DEALLOCATE name_cursor
go

-- 사용법
select * from conv_tables_fields