I have been using the following code for a long time... the original version created a procedure and I modified it to be a basic execution I could run on any database to find out table schema...
This code has helped me now with two job transitions where on my first day I could execute the SQL and take the results into a excel and filter by column name... it has been very useful to see a list of tables that include the same named columns. Here is the code and I hope it finds use for anyone who uses it:
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @status int
DECLARE @PCIntra varchar(100)
DECLARE @PCUltra bit
set @PCIntra = '%'
set @PCUltra = 1
SET @status = 0
DECLARE @TPre varchar(10)
DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint
SET @TPre = ''
SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1
CREATE TABLE #DBAH (TName varchar(100),
CName varchar(100),
CList smallint,
CKind varchar(20),
CSize int,
CWide smallint,
CMore smallint)
INSERT #DBAH
SELECT O.name
, C.name
, C.colid
, T.name
, C.length
, C.prec
, C.scale
FROM sysobjects AS O
JOIN syscolumns AS C
ON O.id = C.id
JOIN systypes AS T
ON C.xusertype = T.xusertype
WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0
AND RTRIM(O.type) = 'U'
AND LEFT(O.name,@TDo3) = @TPre
AND O.name NOT LIKE 'adt%'
AND O.name NOT LIKE '%dtproper%'
AND O.name NOT LIKE 'dt[_]%'
AND (@PCIntra IS NULL OR C.name LIKE @PCIntra)
SET @Retain = @@ERROR IF @status = 0 SET @status = @Retain
SELECT CASE WHEN @PCUltra = 0 THEN SUBSTRING(TName,@TDo4,100) ELSE TName END AS TName, CName, CList, CKind, CSize, CWide, CMore FROM #DBAH ORDER BY TName, CList
DROP TABLE #DBAH
SET NOCOUNT OFF
SELECT (@Status)
GO
--Barry