February 15, 2005 at 1:16 am
Hello Folks
I am looking for basic instruction how to use Meta data. I want to create simple and normal meta data for my database.. such information on database objects etc... I have done search on web and sql book online as well but unfortunately couldn't find any comprehensive doccument.
could any one guide me on this issue.. it is quit urgent too..
Thanks a lot in advance..
SqlIndia
February 15, 2005 at 2:09 am
I'm not sure if I understand you, but see if this goes in your direction:
http://qa.sqlservercentral.com/columnists/mcurnutt/datadictionaryfromwithinsqlserver2000.asp
I bet you'll also find something ehre in the script section. Finally here is script from a user of my site:
/*
SQL-Database QuickDoku
Die hier enthalten Funktionen zeigen in einer Tabelle Informationen zu einer Datenbank zurück.
Die MasterFunction ist die udf_DBDoku()
Über den Aufruf
select TableName,
CASE When Type = 'TABLE' Then TableName Else '' END TableName1,
Type,
XName, -- Name
ISNULL(CAST(ColIx as char(3)),'') as Ix, -- Index des Feldes
ISNULL([XDesc],'') as [Description],
ColdataType,
CASE WHEN ColIsPrimary = '0' THEN '' else ColIsPrimary END ColIsPrimary,
CASE WHEN ColIsNullable = '0' THEN '' else ColIsNullable END ColIsNullable,
CASE WHEN ColIsComputed = '0' THEN '' else ColIsComputed END ColIsComputed,
ColDefault,
ColCollation
from udf_DBDoku()
wird eine Tabelle angezeigt! Diese Tabelle kann man kopieren und dann z.B.: in Excel o.ä. einfügen. So hat man schnell eine druckbare Doku zur Hand.
Die Funktionen wurden schnell entworfen und sind sicher nicht immer optimal, aber sie machen nihre Arbeit. Sie sind frei verwendbar.
Viel Freude beim Dokumentieren. Für FeedBack, Anregung und Kritik erreichen Sie mich unter SAMMYD@GMX.COM
Hendrik Drechsel
*/
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDoku Skriptdatum: 19.10.2004 09:59:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_DBDoku]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_DBDoku]
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuColInfo Skriptdatum: 19.10.2004 09:59:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_DBDokuColInfo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_DBDokuColInfo]
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuColInfoPrimary Skriptdatum: 19.10.2004 09:59:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_DBDokuColInfoPrimary]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_DBDokuColInfoPrimary]
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuDataType Skriptdatum: 19.10.2004 09:59:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_DBDokuDataType]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_DBDokuDataType]
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBStructure Skriptdatum: 19.10.2004 09:59:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_DBStructure]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_DBStructure]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuColInfo Skriptdatum: 19.10.2004 09:59:39 ******/
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuColInfo Skriptdatum: 08.10.2004 12:57:37 ******/
CREATE FUNCTION udf_DBDokuColInfo (@TableName varchar(255))
RETURNS @tbDataBaseStructure TABLE
([TableName] varchar(255) NULL
,[Type] varCHAR(255) NULL
,[ColName] varCHAR(255) NULL
,[ColIx] integer NULL
,[ColDataType] varCHAR(255) NULL
,[ColIsPrimary] CHAR(1) NULL
,[ColIsNullable] CHAR(1) NULL
,[ColIsComputed] CHAR(1) NULL
,[ColDefault] varCHAR(255) NULL
,[ColCollation] varCHAR(255) NULL
,[ColDesc] varchar(2000) )
/*
'** Hendrik Drechsel 2004-10-07 14:44:35
Gibt für die genannte Tabelle die Infos zu den Spalten zurück
Aufruf:
select * from udf_tbDBColInfo('tbAlteration')
*/
AS
BEGIN
-- holt alle ColName und Bechreibungen einer Tabelle
DECLARE curFieldsOfOneTable CURSOR FAST_FORWARD FOR
SELECT --sysobjects.[name] AS TableName,
syscolumns.[name] AS ColdName
FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE (sysobjects.[name] = @TableName)
ORDER BY sysobjects.[name], syscolumns.colid
DECLARE @ColName VARCHAR(255)
-- für die angegebene Tabelle alle Felder holen
OPEN curFieldsOfOneTable
FETCH NEXT FROM curFieldsOfOneTable INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tbDataBaseStructure
SELECT -- DISTINCT
SO.name AS TableName,
'COLUMN' as Type,
CI.COLUMN_NAME AS ColName,
CI.ORDINAL_POSITION AS ColOrdinal,
dbo.udf_DBDokuDataType(Data_Type, Character_Maximum_Length, Numeric_Precision, Numeric_Scale) AS ColDataType,
(select dbo.udf_DBDokuColInfoPrimary(@TableName,@ColName)),
CASE WHEN CI.IS_NULLABLE = 'YES' THEN '1' ELSE '0' END ColNullable,
SC.iscomputed as ColIsComputed,
ISNULL(CI.COLUMN_DEFAULT,'') AS ColDefault,
ISNULL(CI.COLLATION_NAME,'') AS ColCollation,
(SELECT CAST(value AS VARCHAR(1000))FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName, 'column', @ColName))
FROM sysobjects SO
LEFT JOIN INFORMATION_SCHEMA.COLUMNS CI ON SO.name = CI.TABLE_NAME
LEFT JOIN syscolumns SC ON SO.id = SC.id
WHERE (SO.[name] = @TableName) AND (CI.COLUMN_NAME = @ColName) AND (SC.name = @ColName)
FETCH NEXT FROM curFieldsOfOneTable INTO @ColName
END
CLOSE curFieldsOfOneTable
DEALLOCATE curFieldsOfOneTable
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuColInfoPrimary Skriptdatum: 19.10.2004 09:59:39 ******/
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuColInfoPrimary Skriptdatum: 08.10.2004 12:57:37 ******/
CREATE FUNCTION udf_DBDokuColInfoPrimary (@TableName varchar(255), @ColName varchar(255))
RETURNS char(1)
/********************************************************************
** Hendrik Drechsel 2004-10-08 10:28:34
:: Prüfen, ob das Feld in der Tabelle ein primary Key ist
!!
-> TabellenName und feldname
<- '0' oder '1'
**********************************************************************/
AS
BEGIN
DECLARE @sRet as char(1)
IF EXISTS (
SELECT DISTINCT c.name
FROM sysindexes i INNER JOIN syscolumns c
ON c.id = i.id
WHERE (i.status & 0x800) = 0x800 and
c.name IN (
index_col (@TableName, i.indid, 1),
index_col (@TableName, i.indid, 2),
index_col (@TableName, i.indid, 3),
index_col (@TableName, i.indid, 4),
index_col (@TableName, i.indid, 5),
index_col (@TableName, i.indid, 6),
index_col (@TableName, i.indid, 7),
index_col (@TableName, i.indid, 8),
index_col (@TableName, i.indid, 9),
index_col (@TableName, i.indid, 10),
index_col (@TableName, i.indid, 11),
index_col (@TableName, i.indid, 12),
index_col (@TableName, i.indid, 13),
index_col (@TableName, i.indid, 14),
index_col (@TableName, i.indid, 15),
index_col (@TableName, i.indid, 16)) AND c.name = @ColName)
SET @sRet='1'
ELSE
SET @sRet='0'
RETURN @sRet
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuDataType Skriptdatum: 19.10.2004 09:59:39 ******/
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDokuDataType Skriptdatum: 08.10.2004 12:57:37 ******/
CREATE FUNCTION dbo.udf_DBDokuDataType
(
@BaseDataType varchar(128) -- base name like int, numeric
, @Character_Maximum_Length int
, @Numeric_Precision int
, @Numeric_Scale int
) RETURNS varchar(255) -- Data type name like 'numeric (15, 3)'
WITH SCHEMABINDING
/********************************************************************
** Hendrik Drechsel 2004-10-08 12:29:00
:: Liefert den Datentyp als String zurück
<- String
**********************************************************************/
AS BEGIN
RETURN LTRIM(RTRIM(@BaseDataType))
+ CASE WHEN @BaseDataType in ('char', 'varchar', 'nvarchar', 'nchar')
THEN '('
+ CONVERT (varchar(4)
, @Character_Maximum_Length)
+ ')'
WHEN @BaseDataType in ('numeric', 'decimal')
THEN '('
+ Convert(varchar(4), @Numeric_Precision)
+ ', ' + convert(varchar(4), @Numeric_scale)
+ ')'
ELSE ''
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBStructure Skriptdatum: 19.10.2004 09:59:39 ******/
CREATE FUNCTION udf_DBStructure ()
RETURNS @tbDataBaseStructure TABLE
([TableName] varchar(1000) NULL,
oType varCHAR(1000) NULL,
oName varchar(1000) NULL,
[value] varchar(1000) NULL)
/*
** Hendrik Drechsel 2004-08-25 08:58:41
:: liefert die Struktur der Datenbank
Aufruf: select * from udf_DBStructure()
*/
AS
BEGIN
--Select database table names from sysObjects system table.
-- xtype C CHECK constraint
-- D Default or DEFAULT constraint
-- L Log
-- F Foreign key
-- FN Scalar function
-- IF Inline table-function
-- K Primary key/Unique (In SQL6.5 and earlier.)
-- P Stored procedure
-- PK Primary key (SQL7 and up)
-- RF Replication filter stored procedure
-- R Rule
-- S System table
-- TF Multi-step table-function
-- TR Trigger
-- U user table
-- UQ UNIQUE constraint (type is K)
-- V View
-- X Extended stored procedure
-- TYP User-defined datatype
-- IDX Index
-- STA Statistics
-- FIL Object-less file
DECLARE cur CURSOR FAST_FORWARD FOR
(SELECT [name] FROM [dbo].[sysobjects] WHERE [xtype] IN('U') AND [category] <> 2) -- holt alle Namen der UserTables
DECLARE @TableName VARCHAR(1000) --Local Variable used for tablename.
--Enumerate tables in cursor adding details of each into a temporary table.
OPEN cur
FETCH NEXT FROM cur INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tbDataBaseStructure
SELECT @TableName, objtype, objname, CAST(value AS VARCHAR(1000))
FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName, null, default)
INSERT INTO @tbDataBaseStructure
SELECT @TableName, objtype, objname, CAST(value AS VARCHAR(1000))
FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName, 'column', default)
FETCH NEXT FROM cur INTO @TableName
END
CLOSE cur
DEALLOCATE cur
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDoku Skriptdatum: 19.10.2004 09:59:39 ******/
/****** Objekt: Benutzerdefinierte Funktion dbo.udf_DBDoku Skriptdatum: 08.10.2004 12:57:37 ******/
CREATE FUNCTION udf_DBDoku ()
RETURNS @tbReturn TABLE
([TableName] varchar(255) NULL
,[Type] varCHAR(255) NULL
,[XName] varCHAR(255) NULL
,[ColIx] integer NULL
,[ColDataType] varCHAR(255) NULL
,[ColIsPrimary] CHAR(1) NULL
,[ColIsNullable] CHAR(1) NULL
,[ColIsComputed] CHAR(1) NULL
,[ColDefault] varCHAR(255) NULL
,[ColCollation] varCHAR(255) NULL
,[XDesc] varchar(2000) )
/*
'** Hendrik Drechsel 2004-10-07 08:44:14
:: liefert die Dokudaten zur Datenbank
Beispiel-Aufruf:
select TableName,
CASE When Type = 'TABLE' Then TableName Else '' END TableName1,
Type,
XName, -- Name
ISNULL(CAST(ColIx as char(3)),'') as Ix, -- Index des Feldes
ISNULL([XDesc],'') as [Description],
ColdataType,
CASE WHEN ColIsPrimary = '0' THEN '' else ColIsPrimary END ColIsPrimary,
CASE WHEN ColIsNullable = '0' THEN '' else ColIsNullable END ColIsNullable,
CASE WHEN ColIsComputed = '0' THEN '' else ColIsComputed END ColIsComputed,
ColDefault,
ColCollation
from udf_DBDoku()
where type = 'trigger'
*/
AS
BEGIN
-- holt alle Namen der UserTables nach cur
DECLARE curAllTables CURSOR FAST_FORWARD FOR
SELECT [name] FROM sysobjects WHERE ((xtype IN('U')) AND (category <> 2)) ORDER By [name]
DECLARE @TableName VARCHAR(255) --Local Variable used for tablename.
DECLARE @NTableName NVARCHAR(255)
-- für jede tabelle die Details holen und in @tbReturn eintragen
OPEN curAllTables
FETCH NEXT FROM curAllTables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NTableName = @TableName -- wegen UNICODE
-- beschaffe Tabellendaten
INSERT INTO @tbReturn
SELECT @TableName,
'TABLE',
'',
NULL,
'',
'',
'',
'',
'',
'',
(SELECT CAST(value AS VARCHAR(1000)) FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName, null, default))
-- BESCHAFFE TRIGGER
INSERT INTO @tbReturn
SELECT @TableName,
'TRIGGER',
TR.[Name] as TriggerName,
NULL,
'',
'',
'',
'',
'',
'',
CASE WHEN 1=OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled') THEN 'Disabled' ELSE 'Enabled' END TriggerStatus
FROM sysobjects T
INNER JOIN sysobjects TR on t.[ID] = TR.parent_obj
WHERE (T.xtype = 'U' or T.XType = 'V') AND (TR.xtype = 'TR') and (T.[name] = @TableName )
-- beschaffe Spaltendaten zur Tabelle
INSERT INTO @tbReturn
SELECT @TableName,
Type,
ColName,
ColIx,
ColDataType,
ColIsPrimary,
ColIsNullable,
ColIsComputed,
ColDefault,
ColCollation,
ISNULL(ColDesc,'')
FROM udf_DBDokuColInfo(@TableName)
FETCH NEXT FROM curAllTables INTO @TableName
END
CLOSE curAllTables
DEALLOCATE curAllTables
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 15, 2005 at 11:07 pm
Thanks!!! Frank Kalis
I really appreciate this help... could you know how and where can i access to more basic information on this topic..?
Swarn
SqlIndia
February 16, 2005 at 2:41 am
Hm, have you read through BOL on Meta data yet?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 17, 2005 at 9:19 am
This may not be at all what you're looking for, but I have been *really* impressed with the ApexSQL Documentation product - creates a helpful out of your database! Not very friendly to program against, of course, but is a very nice reference to have.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply