|
Just what I needed. I hate coding SQL scripts. Your's does the job for getting me a nice formatted display of the table definitions.
Tom
|
|
|
|
|
Some suggestions for versions ahead...
- Support for SP's/Functions/Triggers
- Support to save doc in other formates like PDF/DOC/Excel/etc
- Customizable output format.
!!BHAVINK!!
|
|
|
|
|
Really helps to understand a lot in database structure.
D V Bava
|
|
|
|
|
|
Hi Nitin,
Is it possible with oracle?
or you can point to any link.
Dipak.
D V Bava
|
|
|
|
|
very very good
go ahead
microsoft certified database administrator"kingchemical"
be good see good
|
|
|
|
|
I add link from fk column to the referenced table, to add it U nead to change 3 places:
1. change the #Columns declareation to:
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000), refName varchar(155), refObj varchar(155), refCol varchar(155), refOnerTable varchar(155))
2. the #column insert into query:
else if @SqlVersion = '2005' <br />
begin<br />
insert into #Columns (Name, Type, Nullable, [description],refName, refObj, refCol, refOnerTable )<br />
--FOR 2005 <br />
Select c.name, <br />
type_name(user_type_id) + (<br />
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')<br />
then '(' + cast(max_length as varchar) + ')' <br />
when type_name(user_type_id) = 'decimal' <br />
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')' <br />
else ''<br />
end <br />
), <br />
case when is_nullable = 1 then 'Y' else 'N' end,<br />
cast(p.value as varchar(4000)),<br />
f.name, <br />
object_name(fc.referenced_object_id) , <br />
COL_NAME (fc.referenced_object_id, fc.referenced_column_id) ,<br />
'[' +fs.name + '].[' + fo.name + ']' <br />
from sys.columns c<br />
inner join #Tables t on t.object_id = c.object_id<br />
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id = c.column_id and p.name = 'MS_Description' <br />
left outer join sys.foreign_key_columns fc on t.object_id = fc.parent_object_id <br />
AND c.column_id = fc.parent_column_id<br />
left outer join sys.foreign_keys f on t.object_id = f.parent_object_id<br />
and f.object_id = fc.constraint_object_id <br />
left outer join sys.objects fo on fo.object_id = f.referenced_object_id<br />
left outer join sys.schemas fs on fs.schema_id = fo.schema_id <br />
where t.id = @i<br />
order by c.column_id
3. the column display to:
select @Output =<br />
case WHEN refObj is null then<br />
'<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(Nullable,'N') + '</td><td>' + isnull([description],'') + '</td></tr>' <br />
else '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px" title="'+isnull(refName, '')+'"><a href="#Table:' + isnull(refOnerTable,'') + '">' + isnull(name,'') + '</a></td><td width="150px">' + upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Nullable,'N') + '</td><td>' + isnull([description],'') + '</td></tr>' <br />
end<br />
from #Columns where id = @j
as U can see I add the link only fot the 2005 server, the 2000 act as usual (or U change the 2000 query...)
thanks for the code
|
|
|
|
|
Really excellent job but I really need to see the stored procedure documentation function, also one more suggestion is to output in other formats also.
Elias Solomonidis
|
|
|
|
|
This is a superbly useful script. As others have asked, I am anxiously waiting the Stored Procedure update.
5 Stars from me!!
|
|
|
|
|
Thank
|
|
|
|
|
I'm working DBA and there are about 40 servers in my management, including both MSSQL2000 and MSSQL2005.
I really need such tools.
However I have noticed some bugs and can make some changes in script:
1. There is wrong name of the column (c.index_column_id) in indexes (for MSSQL2005):
select i.name, '', col_name(i.object_id, c.index_column_id)
Correct one is c.column_id!
2. In case of MSSQL2000 we have no information about the definition of check constraints and can lose some table-level check constraints:
status must be LIKE '0010__00' (not = 00101000), i.e. both 00101000 and 00100100!
3. The definition of check constraints we can take from syscomments in this case.
4. The same we can refer to the definition of default constraints
5. There is no place to take the Description!
I offer:
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [Description] varchar(1000))
and take this information from sysproperties (MSSQL2000) or sys.extended_properties (MSSQL2005)
6. The same we can refer to the description of table and set description to the Summary instead of ...
|
|
|
|
|
Hi PKV1,
Thank you very for you valuable sugesstions.
Points 1 to 4 will be taken care in next update.
For points 5 and 6. It is very good sugesstion, I will try to incrporate them in future updates.
Once again thank you very much.
Regards
Nitin
|
|
|
|
|
I have saved some Description of the Table and its Columns, but these messages are not generated in the document. Can you let it show?
|
|
|
|
|
Script is updated to populate the description. Kindly download latest script
|
|
|
|
|
This is excellent work. You get a 5 from me. This is very well written and very useful. If you do any updates to this,please let me know.
Peace and Practice
|
|
|
|
|
Hi
Script is updated. Kindly check the update log.
|
|
|
|
|
Thanks for a very useful article.
Vikas Mehta
IBN
IBN Technologies Limited
Crystal Corporate , Third Floor, T: +91 20-2426 4182 / 4183
Bibwewadi - Kondwa Road, F: +91 20 2426 4184
Bibwewadi , Pune - 411037 M:+91 9422 458 954
Maharashtra, E:vikas@ibntech.com
India W:www.ibntech.com
|
|
|
|
|
Could you add stored procedures to it?
|
|
|
|
|
Yes I am going to do that
|
|
|
|
|
Nitin,
Great tool - thanks.
Any ideas when you will have a version that includes Stored Procedures?
Also, perhaps an option to also include the table contents (the first X rows) in the report would be useful.
Thanks.
Richard
|
|
|
|
|
Really good work.
I do however have one question. Why do the column descriptions not get shown? I searched in SQL Server to try and find the answer but could not... I couldn't even find where the column descriptions get stored. I took a look in the master table 'sp_help' stored procedure as it also will not show the column descriptions... but to no avail... it just has a remark in it that the description is NULL.. but they are not. I mean I took the time to enter them so they should be accessible.. don't you think?
I do know this, if you create a diagram then the column descriptions will be shown, and can even be edited right in the diagram.
Thanks again.
P.S. just one more thing
it would also be really nice and cool, if stored procedures would get listed. and also maybe even a link to the code of the stored procedure.
|
|
|
|
|
Thanks PKBoone,
SQL don't hvae anything to store the column description. At max what we can do is "Create our own table and insert description of column to that table and then it can be populated to report".
Yes, I will try to add stored procedure with script in the script.
Regards
Nitin
|
|
|
|
|
Hi Nitin,
Thanks for your reply.
Well, the column description has to get stored somewhere. Because you can enter one when editing or creating a table. And it is still there when you go back to edit at a later date. Also, if you create a Diagram the column descriptions also get shown there. I wish I could add pictures to this post so you could see what I mean, but I'm sure you do know anyway. In SQL 2005 the description belongs to the Column Attributes. So like I said, they have to be stored somewhere and since in is accesible by the SQL Manager it should also be accesible by script don't you think?
Thanks again
Philip
|
|
|
|
|
Yes. You are right it stores the description in sysproperties (SQL 2000)
and sys.extended_properties(sql 2005). It stores this value as "MS_Description" property.
Thank you very much for this information. I will update the script for this.
Regards
Nitin
|
|
|
|
|
very useful! thanks, you got my 5
(In the first moment I thought it would be a script do generate a database diagram , would this be possible using sql??)
|
|
|
|