note 44865 deleted from function.mssql-execute by tomsommer
| From: | tomsommer@php.net | Date: | Wed, 18 Aug 2004 19:33:50 +0000 |
| Subject: | note 44865 deleted from function.mssql-execute by tomsommer | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-74921@lists.php.net to get a copy of this message | ||
Note Submitter: tweir at woh dot rr dot com
----
Re: note from marco dot carvalho at NOSPAM dot uni-yoga dot org dot br 07-Jun-2004 05:00
/*
If you have the database rights, create the following stored procedure in the model database
(that way it will be replicated in each new database created). This will give you all of the
user datasets in a database along with the field names, types, lengths, nullability, and
system comments.
To call it,
EXECUTE dbo.proc_data_dictionary @dbname='database_name'
*/
/****** Object: Stored Procedure dbo.proc_data_dictionary Script Date: 6/14/2004 9:39:58 AM
******/
CREATE procedure dbo.proc_data_dictionary @dbname varchar(50)
as
set nocount on
if @dbname<>DB_NAME()
begin
execute('select a.[name] as vDataSet,' +
'b.[name] as vColName,' +
'c.[name] as vColType,' +
'cast(b.length as varchar(4)) as vColLength,' +
'case when isnullable=1 then ''NULL'' else ''NOT
NULL'' end as vColNull,' +
'isnull(cast(d.value as varchar(500)),'''') as vComments
' +
'from ' + @dbname + '.dbo.sysobjects a (nolock) ' +
'join ' + @dbname + '.dbo.syscolumns b (nolock) on a.id=b.id ' +
'join ' + @dbname + '.dbo.systypes c (nolock) on b.xtype=c.xtype
' +
'left join ' + @dbname + '.dbo.sysproperties d (nolock) on a.id=d.id
and b.colid=d.smallid ' +
'where a.xtype=''U'' and
a.[name]<>''dtproperties'' ' +
'order by a.[name],b.colid')
end
else begin
select a.[name] as vDataSet,
b.[name] as vColName,
c.[name] as vColType,
cast(b.length as varchar(4)) as vColLength,
case when isnullable=1 then 'NULL' else 'NOT NULL' end as vColNull,
isnull(cast(d.value as varchar(500)),'') as vComments
from dbo.sysobjects a (nolock)
join dbo.syscolumns b (nolock) on a.id=b.id
join dbo.systypes c (nolock) on b.xtype=c.xtype
left join dbo.sysproperties d (nolock) on a.id=d.id and b.colid=d.smallid
left join dbo.tDataSets e (nolock) on a.[name] = e.vDataSet
where a.xtype='U' and a.[name]<>'dtproperties' and
left(UPPER(isnull(e.vDestDesc,'')),14) <> 'DO NOT
DISPLAY'
order by a.[name],b.colid
end
GO
/*
Trevor Weir,
MS SQL DBA ,
CareSource
mailto:tweir@woh.rr.com
*/