|
Muchas gracias por el código q publicaste...
En esta nueva versión cambié palabras en inglés a español y con la ayuda del agregado de Store Procedures propuesto por el otro compañero, le agregué store procedures para SQL Srv 2005, además muestra el código de los store procedures
http://rapidshare.com/files/322251328/DB_Document_Version_1.2.sql[^]
<br />
Truncate Table #StoredProcedures<br />
IF @SqlVersion = '2000' <br />
BEGIN<br />
INSERT INTO #StoredProcedures(object_id, name, type, [description], Code)<br />
SELECT so.id, '[dbo].[' + CAST(so.name AS varchar(155)) + ']', 'Procedimiento Almacenado', (SELECT CAST(sp.value AS varchar(4000)) FROM sysproperties sp WHERE sp.id = so.id) AS Comment, '' <br />
FROM sysobjects so WHERE so.type ='P' AND so.Category <> 2<br />
END<br />
else if @SqlVersion = '2005' <br />
begin<br />
insert into #StoredProcedures (Object_id, Name, Type, [description], Code)<br />
--FOR 2005<br />
Select o.object_id, '[' + s.name + '].[' + o.name + ']', <br />
'Procedimiento Almacenado', <br />
cast(p.value as varchar(4000)),<br />
ISNULL(smsp.definition, ssmsp.definition)<br />
from sys.objects o <br />
left outer join sys.schemas s on s.schema_id = o.schema_id <br />
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' <br />
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = o.object_id<br />
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = o.object_id<br />
where type = 'P' <br />
order by type, s.name, o.name<br />
end<br />
<br />
SET @MaxK = (SELECT COUNT(id) FROM #StoredProcedures)<br />
SET @k = 1<br />
set @sr = 1<br />
<br />
print '<table border="0" cellspacing="0" cellpadding="0" width="100%" id = "index2"><tr><td><b>Procedimientos Almacenados</b></td></tr></table>' <br />
print '<table border="0" cellspacing="1" cellpadding="0" width="100%"><tr><th>Num</th><th>Nombre</th><th>Tipo</th></tr>' <br />
set @Output = ''<br />
set @last = ''<br />
set @current = ''<br />
WHILE(@k <= @MaxK)<br />
BEGIN<br />
SELECT @Output = '<tr><td align="center">' + Cast((@k) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>' <br />
from #StoredProcedures where id = @k <br />
PRINT @Output<br />
SET @k = @k + 1<br />
END<br />
print '</table><br />'<br />
Truncate Table #SpColumns<br />
SET @k = 1<br />
WHILE(@k <= @maxk)<br />
BEGIN<br />
--table header<br />
SELECT @Output = '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>', @description = [description]<br />
FROM #StoredProcedures WHERE id = @k<br />
<br />
PRINT '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="100%"><tr><td align="right"><a href="#index2">Indice</a></td></tr>'<br />
PRINT @Output<br />
PRINT '</table><br />'<br />
PRINT '<table border="0" cellspacing="0" cellpadding="0" width="100%"><tr><td><b>Descripcion</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />' <br />
<br />
--table SpColumns<br />
<br />
-- look in the syscoloumns table to get the coloumn (input, output parameters) names<br />
INSERT INTO #SpColumns (Name, Type, Output)<br />
--FOR 2000<br />
SELECT sc.name, st.name + (<br />
CASE WHEN (st.name = 'varchar' or st.name = 'nvarchar' or st.name ='char' or st.name ='nchar')<br />
THEN '(' + cast(sc.length as varchar) + ')' <br />
WHEN st.name = 'decimal' <br />
THEN '(' + cast(sc.prec as varchar) + ',' + cast(sc.scale as varchar) + ')' <br />
ELSE ''<br />
END ) <br />
, isOutparam <br />
FROM syscolumns sc, master..systypes st, sysobjects so <br />
WHERE so.id = (SELECT SP.object_id FROM #StoredProcedures SP<br />
WHERE SP.ID = @k)<br />
AND sc.id = so.id <br />
AND sc.xtype = st.xtype <br />
<br />
SET @maxj = @@rowcount<br />
SET @j = 1<br />
<br />
PRINT '<table border="0" cellspacing="0" cellpadding="0" width="100%"><tr><td><b>Columnas</b></td></tr></table>' <br />
PRINT '<table border="0" cellspacing="1" cellpadding="0" width="100%"><tr><th>Num</th><th>Nombre</th><th>Tipo</th><th>Col. Salida</th></tr>' <br />
<br />
WHILE(@j <= @maxj)<br />
BEGIN<br />
SELECT @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Output,'0') + '</td></tr>' <br />
FROM #SpColumns WHERE id = @j<br />
<br />
PRINT @Output <br />
SET @j = @j + 1;<br />
END<br />
<br />
-- Obtain sp code<br />
<br />
declare @LargoCodigoSP as int<br />
set @LargoCodigoSP = (select LEN(Code) FROM #StoredProcedures WHERE id = @k)<br />
declare @InicioBloqueSp as int, @LargoBloqueSP as int<br />
declare @Out1 as varchar(max)<br />
set @InicioBloqueSP = 1<br />
if (@LargoCodigoSp > 7900)<br />
Begin<br />
set @LargoBloqueSP = 7900<br />
End<br />
Else<br />
Begin<br />
set @LargoBloqueSP = @LargoCodigoSP<br />
End<br />
PRINT '<tr><td align="left" colspan="4"><div class="code-comment">'<br />
while (@InicioBloqueSP < @LargoCodigoSP)<br />
Begin<br />
set @Out1 = (select substring(Code,@InicioBloqueSP,@LargoBloqueSP) FROM #StoredProcedures WHERE id = @k)<br />
--reemplazar los cambios de linea<br />
set @Out1 = (Select REPLACE(@Out1,'<br />
', '<br />'))<br />
print @Out1<br />
set @InicioBloqueSP = @InicioBloqueSP + @LargoBloqueSP<br />
set @LargoBloqueSP = 7900<br />
if (@LargoBloqueSP > (@LargoCodigoSP - @InicioBloqueSP))<br />
Begin<br />
set @LargoBloqueSP = @LargoCodigoSP - @InicioBloqueSP + 1<br />
End<br />
End<br />
PRINT '</div></td></tr>'<br />
<br />
PRINT '</table><br />'<br />
SET @k = @k + 1;<br />
END
Ronald Fallas
|
|
|
|
|
Hay una pulguita con el truncate de #SpColumns, hay q ponerlo dentro del While
<br />
Truncate Table #SpColumns<br />
INSERT INTO #SpColumns (Name, Type, Output)
Ronald Fallas
|
|
|
|
|
Very useful, it would be perfect if could add sql 2005/2008 code for table-valued & scalar functions, and stored procedures. (Also could be turned into a stored procedure with parameter of output html file, and load or print option.)
|
|
|
|
|
Nice job. Makes reading DB structure quite easy.
|
|
|
|
|
I'm using MSSQL 2005,
and the system view 'sys.columns' returns its column length
for nchar and nvarchar are doubled actual length.
so, this code should be patched like below
ver. 18-Feb-2008
... skipped ...
Select c.name,
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
... skipped ...
should be,
... skipped ...
Select c.name,
type_name(user_type_id) + (
case
when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) ='char' )
then '(' + cast(max_length as varchar) + ')'
when (type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length/2 as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
... skipped ...
|
|
|
|
|
You have saved me days of work. Thank you for this excellent code!!
|
|
|
|
|
Stunning piece of script saved me loads of time.
Just in case anyone is interested I wanted to list stored procedures as well so I added some extra code into do this.
I've only done this for sql server 2000 so it may need tweaking for other versions
First create some temporary tables and a some variables to use
DECLARE @k int, @MaxK int
CREATE Table #StoredProcedures(id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(30), [description] varchar(4000))
CREATE TABLE #SpColumns(id int identity(1,1), Name varchar(155), Type Varchar(155), Output varchar(2))
Populate the #StoredProcedures table
--look in the sys objects table for objects of type 'P' stored procedures and not category 2
--(category 2 seem to be system stored procedures) also look in the sysproperties
--table for any description of the stored procedure and insert into the temp table
IF @SqlVersion = '2000'
BEGIN
INSERT INTO #StoredProcedures(object_id, name, type, [description])
SELECT so.id, '[dbo].[' + CAST(so.name AS varchar(155)) + ']', 'Stored Procedure', (SELECT CAST(sp.value AS varchar(4000)) FROM sysproperties sp WHERE sp.id = so.id) AS Comment
FROM sysobjects so WHERE so.type ='P' AND so.Category <> 2
END
Display the links at the top of the page (I put this just under the under the block that display's the list of tables and views before the close table)
SET @MaxK = (SELECT COUNT(id) FROM #StoredProcedures)
SET @k = 1
WHILE(@k <= @MaxK)
BEGIN
SELECT @Output = '<tr><td align="center">' + Cast((@k) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>'
from #StoredProcedures where id = @k
PRINT @Output
SET @k = @k + 1
END
Then display the stored procedure details at the bottom of the page
SET @k = 1
WHILE(@k <= @maxk)
BEGIN
--table header
SELECT @Output = '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>', @description = [description]
FROM #StoredProcedures WHERE id = @k
PRINT '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td align="right"><a href="#index">Index</a></td></tr>'
PRINT @Output
PRINT '</table><br />'
PRINT '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Description</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />'
--table SpColumns
TRUNCATE TABLE #SpColumns
IF @SqlVersion = '2000'
BEGIN
-- look in the syscoloumns table to get the coloumn (input, output parameters) names
INSERT INTO #SpColumns (Name, Type, Output)
--FOR 2000
SELECT sc.name, st.name + (
CASE WHEN (st.name = 'varchar' or st.name = 'nvarchar' or st.name ='char' or st.name ='nchar')
THEN '(' + cast(sc.length as varchar) + ')'
WHEN st.name = 'decimal'
THEN '(' + cast(sc.prec as varchar) + ',' + cast(sc.scale as varchar) + ')'
ELSE ''
END )
, isOutparam
FROM syscolumns sc, master..systypes st, sysobjects so
WHERE so.id = (SELECT SP.object_id FROM #StoredProcedures SP
WHERE SP.ID = @k)
AND sc.id = so.id
AND sc.xtype = st.xtype
END
SET @maxj = @@rowcount
SET @j = 1
PRINT '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Table Columns</b></td></tr></table>'
PRINT '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Output</th></tr>'
WHILE(@j <= @maxj)
BEGIN
SELECT @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Output,'0') + '</td></tr>'
FROM #SpColumns WHERE id = @j
PRINT @Output
SET @j = @j + 1;
END
PRINT '</table><br />'
SET @k = @k + 1;
END
Then finally drop the temporary tables
DROP TABLE #StoredProcedures
DROP TABLE #SpColumns
modified on Wednesday, June 24, 2009 5:34 AM
|
|
|
|
|
Thanks!
Dave Noderer
daven@computerways.com
|
|
|
|
|
Very slick
modified on Thursday, April 30, 2009 5:07 PM
|
|
|
|
|
SQLDocSharp
An interactive tool designed to generate the SQL Server 2005/2008 documentation.
This is a free solution...!
http://www.amitchaudhary.com/ .
|
|
|
|
|
Thanks for this great work.
it is very useful for me.
For any non English languages (Arabic, Chinese,...) descriptions, the @output and varchar(4000)should be converted to nvarchar(2000) or nvarchar(4000) if necessary.
thanks for the great work again  
Hussien Sharaf
|
|
|
|
|
Dear Nitin Patel,
I m having Sql server 2000 on my personal Laptop , and my client has sended Database backup in 2005 formate. Plz tell me can i restore it to my local machine having Sql Server 2000 if yes plz tell me
, i welcome any suggestion
Thank You
Parag
|
|
|
|
|
You just saved me several hours of work. Excellent and very useful bit of coding. 
|
|
|
|
|
What can i say... ?, it's a great job, very helfull
thanks a lot.
|
|
|
|
|
Well done Nitipatel
This has saved me hours of work.
Thanks
Mark Harby
Nottingham. UK
|
|
|
|
|
You solved my problem at work. Thank you very much. Keep up the good work.
|
|
|
|
|
|
Thanks, It Is very useful.
|
|
|
|
|
|
Thank you for sharing it. It's most helpful.
|
|
|
|
|
i was about to buy a complete package for just this function.. Thanks a million..
|
|
|
|
|
|
Wow!
Thanks..
Regards,
OktaEndy
|
|
|
|
|
Just what he needed. Tanks.
C# , SQL , ASP.NET,l AJAX
|
|
|
|
|
I have a meeting this morning in which I have to present this information. This is a total life saver
Great coding
Thanks
-WM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
