sp_desc

DROP PROCEDURE sp_desc
GO
CREATE PROCEDURE sp_desc @objname VARCHAR(256)
AS
DECLARE @a VARCHAR(256),@b VARCHAR(120),@ss VARCHAR(256),@owner VARCHAR(64);

SELECT @a=v.name,@b=v.type_desc,@ss=ss.name,@owner=sp.name
FROM sys.all_objects v 
JOIN sys.schemas ss on
v.schema_id=ss.schema_id
JOIN sys.database_principals sp on
ss.principal_id=sp.principal_id
WHERE v.name=@objname
SELECT @a AS object_name,@b AS object_type,@ss as schema_name,@owner as owner;

SELECT c.name AS field_name,st.name [type],c.max_length [length],c.collation_name 
FROM sys.all_objects v 
JOIN sys.all_columns c
ON v.object_id=c.object_id
JOIN sys.types st
ON c.user_type_id=st.user_type_id
WHERE v.name=@objname
go