Prev: Best way to store vectors?
Next: Cursor problem
From: SQL on 8 Jul 2010 18:41
How do I find out (or list all tables and corresponding fields) that
uses maximum/sql server default values whenever the objects are
created in a particular database.

For ex. If someone has created a user object with field name for ex.
First Name with SQL Server default i.e. varchar(256) vs using
varchar(50). How do I get all the filed names in the database like
these?
From: Eric Isaacs on 8 Jul 2010 20:35
I don't exactly understand your question, but I think you're trying to
get to data you can extract from INFORMATIONSCHEMA.COLUMNS...

SELECT * FROM information_schema.columns

-Eric Isaacs
From: Dan Guzman on 8 Jul 2010 20:43
> For ex. If someone has created a user object with field name for ex.
> First Name with SQL Server default i.e. varchar(256) vs using
> varchar(50). How do I get all the filed names in the database like
> these?

The actual default varchar length is 1. Both of the following are
equivalent:

CREATE TABLE dbo.foo(bar varchar);
CREATE TABLE dbo.foo(bar varchar(1));

SSMS uses a 50 character length is an arbitrary default, though.

You can list columns with a varchar(256) specification using the query
below.

SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types ty ON ty.system_type_id = c.system_type_id
WHERE
ty.name = N'varchar'
AND c.max_length = 256;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



 | 
Pages: 1
Prev: Best way to store vectors?
Next: Cursor problem