July 12, 2002 at 12:33 pm
One of the developers here had a question about large varchar fields in the database.
Since I have been on this project for less than two weeks, I don't really know all of their database schemas yet. I decided that it would be useful to see if I could pull this information together and build something generic that could be used on any database.
This query (which could be thrown into a cursor in a stored proc, too) will give a list of every column in the database, its datatype, and size.
In this case, it is obviously ordered by size first.
SELECT DISTINCT
LEFT(a.name, 30) AS fieldname, left(c.name,20) AS datatype,
a.length AS size, b.name AS tablename
FROM syscolumns a INNER JOIN
systypes c ON a.xtype = c.xusertype INNER JOIN
sysobjects b ON a.id = b.id
WHERE (b.xtype = 'U') AND (b.name <> 'dtproperties')
ORDER BY size desc, tablename, fieldname
Hope this helps someone!!
Lisa
July 12, 2002 at 4:55 pm
Another option that doesn't rely on system tables is the INFORMATION_SCHEMA.Columns view. It's basically the same as the query you've generated, but Microsoft should keep INFORMATION_SCHEMA.Columns the same in the future because it's based on a specification from the SQL-92 standard. They reserve the right to change system tables as needed.
With that said, I'm usually going against the system tables.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 15, 2002 at 9:06 am
A few weeks back, I found the folowing script. Where? In SQL Server Central!!!
SELECT TOP 100 PERCENT
sysobjects.name AS Table_Name,
syscolumns.name AS Column_Name,
systypes.name AS Data_Type,
syscolumns.length
FROMsysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype
WHERE(NOT (dbo.systypes.name LIKE N'sysname'))
AND (NOT (dbo.sysobjects.name LIKE N's%'))
AND (NOT (dbo.sysobjects.name LIKE N'dt_%'))
ORDER BY sysobjects.name, syscolumns.name
I just run it against a particular db and it gives you table name, column anme, data type and lenght. Hope this help
Happy to return the favor when I needed this one. Good luck
July 15, 2002 at 10:06 am
i get several versions of each entry for the second version, but require this version as it allows me to view user defined types too. any ideas?
m
M Saunders
Web/DB Developer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply