|
SELECT
st.name AS [Name],
sst.name AS [Schema],
st.user_type_id AS [ID],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND st.max_length <> -1 THEN st.max_length/2 ELSE st.max_length END AS int) AS [Length],
CAST(st.precision AS int) AS [NumericPrecision],
CAST(st.scale AS int) AS [NumericScale],
st.max_length AS [MaxLength],
st.is_nullable AS [Nullable],
(case when st.default_object_id = 0 then N'' else schema_name(def.schema_id) end) AS [DefaultSchema],
baset.name AS [SystemType]
FROM
sys.types AS st
INNER JOIN sys.schemas AS sst ON sst.schema_id = st.schema_id
LEFT OUTER JOIN sys.database_principals AS s1st ON s1st.principal_id = ISNULL(st.principal_id, (TYPEPROPERTY(QUOTENAME(SCHEMA_NAME(st.schema_id)) + '.' + QUOTENAME(st.name), 'OwnerId')))
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = st.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = st.system_type_id) and (baset.user_type_id = st.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.objects AS def ON def.object_id = st.default_object_id
LEFT OUTER JOIN sys.objects AS rul ON rul.object_id = st.rule_object_id
WHERE
(st.schema_id!=4 and st.system_type_id!=240 and st.user_type_id != st.system_type_id and st.is_table_type != 1)
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist
Open source projects:
DBScripter - Library for scripting SQL Server database objects
Please, do not forget vote