#3 sql server 2005 tables listed incompletely

[Anonymous]

Because of the changes to schemas between SqlServer 2000 and 2005, the table select statement you currently have fails when the user matching the schema is removed.

The following *may* address the problem, but as it only provides the schema name and not the actual owner, I'm not sure if completely solves the problem. Joining to sys.database_principals via the principal_id may be what you need.

select sp.name as Table_Name,
ssp.name as Owner
from sys.tables sp WITH (NOLOCK)
INNER join sys.schemas as ssp WITH (NOLOCK) on sp.schema_id = ssp.schema_id

Reference: http://msdn.microsoft.com/en-us/library/ms190387.aspx particularly the statement: New DDL statements can introduce complexities to system metadata that are not accurately reflected in old catalog views such as sysobjects. In this example, the user ID and schema name returned by sysobjects are out of sync, and do not reflect the distinction between user and schema introduced in SQL Server 2005.

[Aaron]

In addition to the table alterations, the columns query also needs to be updated. The following *seems* to work.

SELECT
sc.object_ID as ID,
sc.column_id as [Order],
so.Name as Table_Name,
sc.Name,
st.Name AS Data_Type,
CASE
WHEN st.Name IN ('char', 'varchar')
THEN sc.max_Length
WHEN st.Name IN ('nchar', 'nvarchar')
THEN (sc.max_Length/2)
ELSE NULL
END AS Length,
CASE
WHEN st.Name IN ('Decimal', 'Numeric')
THEN sc.precision
ELSE NULL
END AS [Precision],
CASE
WHEN st.Name IN ('Decimal', 'Numeric')
THEN sc.Scale
ELSE NULL
END AS [Scale],
CASE
WHEN sc.Is_Nullable = 0
THEN 'false'
ELSE 'true'
END AS IsNullable,
CASE WHEN CAST(COLUMNPROPERTY(sc.object_id, sc.name, N'IsIdentity') AS bit) = 0 THEN 'false' ELSE 'true' END AS [Identity],
CAST(case COLUMNPROPERTY(sc.object_id, sc.name, N'IsIdentity') when 1 then ident_seed(QUOTENAME(sss.name) + '.' + QUOTENAME(so.name)) else 0 end AS bigint) AS [IdentitySeed],
CAST(case COLUMNPROPERTY(sc.object_id, sc.name, N'IsIdentity') when 1 then ident_incr(QUOTENAME(sss.name) + '.' + QUOTENAME(so.name)) else 0 end AS bigint) AS [IdentityIncrement],
D.Name AS [Default_Name],
REPLACE(D.Value, '''', '') as [Default_Value]
FROM
sys.tables so WITH (NOLOCK)
INNER join sys.schemas as sss WITH (NOLOCK) on so.schema_id = sss.schema_id
inner join sys.server_principals as stbl WITH (NOLOCK) on sss.principal_id = stbl.principal_id
INNER JOIN sys.columns sc WITH (NOLOCK) on sc.object_id = so.object_ID
INNER JOIN sys.types st WITH (NOLOCK) on st.system_type_id = sc.system_type_id and st.user_type_id = sc.user_type_id
LEFT JOIN (
SELECT
so.Name,
so.parent_obj AS Parent_Table_ID,
scon.colid AS ColumnID,
REPLACE(REPLACE(REPLACE(REPLACE(c.Text, '()', '~~'), '(', ''), ')', ''), '~~', '()') AS Value
FROM
dbo.sysobjects so WITH (NOLOCK)
INNER JOIN dbo.sysconstraints scon WITH (NOLOCK) on scon.constid = so.id
INNER JOIN dbo.syscomments c WITH (NOLOCK) ON c.id = scon.constid
WHERE
so.xtype = 'D'
) AS D on D.Parent_Table_ID = so.object_ID
AND
D.ColumnID = sc.Column_ID
WHERE
st.Name != 'sysname'
ORDER BY
so.Name,
sc.column_id