Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List all columns in server Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 11:52 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
Points: 357, Visits: 310
Comments posted to this topic are about the item List all columns in server
Post #995745
Posted Thursday, September 30, 2010 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 1:25 PM
Points: 31, Visits: 30
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
Post #995912
Posted Thursday, September 30, 2010 9:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 30, 2010 11:03 AM
Points: 1, Visits: 3
Barry

Thank you for this little but POWERFUL code. I currently work with a database that was specifically designed in mayhem and understanding the schema is very important.


Thanks
Bryan


Barry J. Stratta (9/30/2010)
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
Post #996019
Posted Thursday, September 30, 2010 12:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 1:25 PM
Points: 31, Visits: 30
Bryan.Bowden (9/30/2010)
Barry

Thank you for this little but POWERFUL code.


No problem... it's one of my favorites too. I have it saved on at least 4 different flash drive; lol
Post #996149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse