Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SQL2000, дайте плиз примеры получения системных данных (базы, таблицы, колонки).  [new]
Pvase
Member

Откуда:
Сообщений: 990
Задача:
1. Получить список всех баз на сервере.
2. Получить список всех таблиц (если можно, то только пользовательских) в определенной базе.
3. Получить список всех полей в таблице (по возможности с типом данных).

Заранее большой сенкс за примиеры.
ЗЫ
Извините если вопрос ламерский.
13 фев 06, 11:25    [2349077]     Ответить | Цитировать Сообщить модератору
 Re: SQL2000, дайте плиз примеры получения системных данных (базы, таблицы, колонки).  [new]
Paul Sacks
Member

Откуда:
Сообщений: 1105
BOL:

INFORMATION_SCHEMA.TABLES

и т.д.

а можно прямо из системных ьаблиц
13 фев 06, 11:28    [2349100]     Ответить | Цитировать Сообщить модератору
 Re: SQL2000, дайте плиз примеры получения системных данных (базы, таблицы, колонки).  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

см. представления INFORMATION_SCHEMA


Posted via ActualForum NNTP Server 1.3

13 фев 06, 11:29    [2349106]     Ответить | Цитировать Сообщить модератору
 Re: SQL2000, дайте плиз примеры получения системных данных (базы, таблицы, колонки).  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31604
Pvase
1. Получить список всех баз на сервере.
2. Получить список всех таблиц (если можно, то только пользовательских) в определенной базе.
3. Получить список всех полей в таблице (по возможности с типом данных).
См. BOL:

System Tables in the master Database, таблица sysdatabases

System Tables in Every Database, таблицы
sysobjects
syscolumns
systypes
13 фев 06, 11:29    [2349112]     Ответить | Цитировать Сообщить модератору
 Re: SQL2000, дайте плиз примеры получения системных данных (базы, таблицы, колонки).  [new]
superbluesman
Member

Откуда:
Сообщений: 1017
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ft_GetColumnMetaData' AND type = 'TF')
  DROP FUNCTION ft_GetColumnMetaData
GO


-- Возвращает метаданные полей для указанных/ой таблиц/ы
CREATE FUNCTION dbo.ft_GetColumnMetaData
(
@tableName VARCHAR(200),     -- имя таблиц/ы (если не указано, то все таблицы)
@WhatSeekBy INT              -- 1= отбор таблиц по неточному совпадению наименования (LIKE @tableName+'%')
                             -- 2= отбор таблиц по наименованию по вхождению (LIKE '%'+@tableName+'%')
                             -- иначе= отбор таблиц по точному совпадению наименования
)
RETURNS @returnTbl TABLE (table_schema VARCHAR(150),
                        table_name VARCHAR(200),                    -- имя таблицы
                        column_name VARCHAR(200),                   -- имя поля
                        data_type VARCHAR(30),                      -- тип данных поля
                        precision_ INT,                             -- общая длина поля
                        scale INT,                                  -- число знаков после запятой
                        AllowsNull BIT,                             -- 1=допустимость NULL-значений
                        IsIdentity BIT,                             -- 1=это Identity-поле
                        column_default VARCHAR(4000),               -- значение по умолчанию
                        column_description VARCHAR(2000),           -- описание поля
                        IsRowGuidCol BIT,                           -- 1=это RowGuid-поле
                        IsFulltextIndexed BIT,                      -- 1=это RowGuid-поле
                                                                                          primary_key_constraint_name VARCHAR(200),   -- наименование Primary-ограничения
                                                                                                foreign_key_constraint_name VARCHAR(200),   -- наименование Foreign-ограничения
                                                                                                pk_table VARCHAR(200),                      -- имя внешней таблицы
                                                                                          pk_column VARCHAR(200),                     -- имя внешнего поля внешней таблицы
                                                                                          update_rule VARCHAR(50),                    -- действие при обновлении записей
                                                                                          delete_rule VARCHAR(50),                    -- действие при удалении записей
                                                                                          check_constraint_name VARCHAR(200),         -- наименование Check-ограничения
                        ordinal_position INT)                       -- порядковый номер поля при его создании в таблице
AS
BEGIN
DECLARE @tName VARCHAR(200)
DECLARE @tbls TABLE (table_name VARCHAR(200))
DECLARE @colDesriptionTbl TABLE (table_name VARCHAR(200),
                                 column_name VARCHAR(200),
                                 column_description VARCHAR(2000),
                                 precision_ INT,
                                 scale INT,
                                 AllowsNull BIT,
                                 IsIdentity BIT,
                                 IsRowGuidCol BIT,
                                 IsFulltextIndexed BIT)

-- Выбираем основную информацию о полях искомых таблиц:
IF ISNULL(@tableName,'')=''
  BEGIN
    INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES
    INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
          SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
                 CAST(table_name AS VARCHAR(200)) AS table_name,
                 CAST(column_name AS VARCHAR(200)) AS column_name,
                 CAST(data_type AS VARCHAR(30)) AS data_type,
                 column_default,
                 ordinal_position
            FROM INFORMATION_SCHEMA.COLUMNS
  END
ELSE
  IF @WhatSeekBy=1
    BEGIN
      INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE @tableName+'%'
      INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
                  SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
                         CAST(table_name AS VARCHAR(200)) AS table_name,
                         CAST(column_name AS VARCHAR(200)) AS column_name,
                         CAST(data_type AS VARCHAR(30)) AS data_type,
                   column_default,
                         ordinal_position
                    FROM INFORMATION_SCHEMA.COLUMNS
                WHERE table_name LIKE @tableName+'%'
    END
  ELSE
    IF @WhatSeekBy=2
      BEGIN
        INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE '%'+@tableName+'%'
        INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
                          SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
                                 CAST(table_name AS VARCHAR(200)) AS table_name,
                                 CAST(column_name AS VARCHAR(200)) AS column_name,
                                 CAST(data_type AS VARCHAR(30)) AS data_type,
                     column_default,
                                 ordinal_position
                            FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE table_name LIKE '%'+@tableName+'%'
      END
    ELSE
      BEGIN
        INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name=@tableName
        INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
                          SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
                                 CAST(table_name AS VARCHAR(200)) AS table_name,
                                 CAST(column_name AS VARCHAR(200)) AS column_name,
                                 CAST(data_type AS VARCHAR(30)) AS data_type,
                     column_default,
                                 ordinal_position
                            FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE table_name=@tableName
      END

IF @@ROWCOUNT>0
  BEGIN
                DECLARE cur_tbls CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT table_name FROM @tbls
                OPEN cur_tbls
                FETCH NEXT FROM cur_tbls INTO @tName
                WHILE (@@FETCH_STATUS=0)
                  BEGIN
        -- Выбираем информацию об описаниях полей и других свойствах полей:
                    INSERT @colDesriptionTbl (table_name, column_name, column_description, 
                                              precision_, scale, AllowsNull, IsIdentity, IsRowGuidCol, IsFulltextIndexed)
                            SELECT @tName AS table_name, 
                             c.[name] AS column_name,
                             ISNULL(CAST(p.value AS VARCHAR(2000)),'') AS column_description,
                             COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'Precision') AS precision_,
                             ISNULL(COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'Scale'),0) AS scale,
                             COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'AllowsNull') AS AllowsNull,
                             COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsIdentity') AS IsIdentity,
                             COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsRowGuidCol') AS IsRowGuidCol,
                             COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsFulltextIndexed') AS IsFulltextIndexed
                                FROM dbo.syscolumns c
                              LEFT JOIN dbo.sysproperties p ON c.[id]=p.[id] AND c.colid=p.smallid
                                    WHERE       (p.type=4 OR p.type IS NULL) AND c.[id]=OBJECT_ID(@tName)
                
                    FETCH NEXT FROM cur_tbls INTO @tName
                  END
                CLOSE cur_tbls
                DEALLOCATE cur_tbls
                
                
                -- Выбираем информацию о первичных ключах:
                DECLARE @pk TABLE (table_name VARCHAR(200),  column_name VARCHAR(200), primary_key_constraint_name VARCHAR(200))
                INSERT INTO @pk
                        SELECT a.table_name, b.column_name,
                               a.constraint_name AS primary_key_constraint_name
                          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a 
                          INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on b.constraint_name=a.constraint_name 
                            WHERE a.constraint_type='PRIMARY KEY'
                
                -- Выбираем информацию о внешних ключах:
                DECLARE @fk TABLE (table_name VARCHAR(200),  column_name VARCHAR(200), foreign_key_constraint_name VARCHAR(200), 
                                   pk_table VARCHAR(200), pk_column VARCHAR(200), update_rule VARCHAR(50), delete_rule VARCHAR(50))
                INSERT INTO @fk
                        SELECT a.table_name, b.column_name,
                               a.constraint_name AS foreign_key_constraint_name,
                               c.pk_table, c.pk_column, c.update_rule, c.delete_rule
                          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a 
                          INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on b.constraint_name=a.constraint_name 
                          INNER JOIN (SELECT u.constraint_name, k.table_name AS pk_table, k.column_name AS pk_column, 
                                             r.update_rule, r.delete_rule
                                        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
                                        INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.constraint_name=u.constraint_name
                                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON r.unique_constraint_name=k.constraint_name) c
                              ON a.constraint_name=c.constraint_name
                            WHERE a.constraint_type='FOREIGN KEY'
                
                -- Выбираем информацию об других ограничениях, накладываемых на столбцы:
                DECLARE @chk TABLE (table_name VARCHAR(200), column_name VARCHAR(200), check_constraint_name VARCHAR(200))
                INSERT INTO @chk
                        SELECT a.table_name, b.column_name,
                               a.constraint_name AS check_constraint_name
                          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a 
                          INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on b.constraint_name=a.constraint_name
                            WHERE a.constraint_type='CHECK'
                --------------------------------------------------------------------------------------------------------------------
                
                
                
                UPDATE @returnTbl SET column_default=ISNULL(c.column_default,''),
                                      precision_=s.precision_,
                                      scale=s.scale,
                                      AllowsNull=s.AllowsNull,
                                      IsIdentity=s.IsIdentity, 
                                      IsRowGuidCol=s.IsRowGuidCol,
                                      IsFulltextIndexed=s.IsFulltextIndexed,
                                      column_description=s.column_description,
                                      primary_key_constraint_name=ISNULL(p.primary_key_constraint_name,''),
                                      foreign_key_constraint_name=ISNULL(f.foreign_key_constraint_name,''),
                                      pk_table=ISNULL(f.pk_table,''),
                                      pk_column=ISNULL(f.pk_column,''), 
                                      update_rule=ISNULL(f.update_rule,''), 
                                      delete_rule=ISNULL(f.delete_rule,''),
                                      check_constraint_name=ISNULL(h.check_constraint_name,'')
                  FROM @returnTbl c
                  INNER JOIN @colDesriptionTbl s ON c.table_name=s.table_name AND c.column_name=s.column_name
                  LEFT JOIN @pk p ON c.table_name=p.table_name AND c.column_name=p.column_name
                  LEFT JOIN @fk f ON c.table_name=f.table_name AND c.column_name=f.column_name
                  LEFT JOIN @chk h ON c.table_name=h.table_name AND c.column_name=h.column_name
END

RETURN
END

-- Примеры вызова:
/*
SELECT * FROM dbo.ft_GetColumnMetaData(NULL, NULL) ORDER BY table_name, column_name
SELECT * FROM dbo.ft_GetColumnMetaData('PS_company', 0) ORDER BY table_name, column_name
SELECT * FROM dbo.ft_GetColumnMetaData('PS', 1) ORDER BY table_name, column_name
SELECT * FROM dbo.ft_GetColumnMetaData('currency', 2) ORDER BY table_name, column_name
*/
13 фев 06, 12:02    [2349348]     Ответить | Цитировать Сообщить модератору
 Re: SQL2000, дайте плиз примеры получения системных данных (базы, таблицы, колонки).  [new]
homut
Member

Откуда:
Сообщений: 4
все-таки, почему "правильным" порядковым номерам колонки в таблице, который дает sp_columns не всегда соответствуют значениям

ordinal_position в INFORMATION_SCHEMA.COLUMNS
или
colorder в syscolumns (что, я так понимаю, то же самое)

требуется возвратить в процедуру имя поля именно по "правильному" номеру...
16 мар 06, 11:06    [2454580]     Ответить | Цитировать Сообщить модератору
 Re: SQL2000, дайте плиз примеры получения системных данных (базы, таблицы, колонки).  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

автор

все-таки, почему "правильным" порядковым номерам колонки в таблице, который дает sp_columns не всегда соответствуют значениям

ordinal_position в INFORMATION_SCHEMA.COLUMNS
или
colorder в syscolumns (что, я так понимаю, то же самое)

требуется возвратить в процедуру имя поля именно по "правильному" номеру...

а Вы взгляните на текст sp_columns и сравните его с INORMATION_SCHEMA.COLUMNS


Posted via ActualForum NNTP Server 1.3

16 мар 06, 11:11    [2454622]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить