List all columns in server

• shanu.hoosen

  SSCrazy

  Points: 2227

  More actions

  September 29, 2010 at 11:52 pm

  #229871

  Comments posted to this topic are about the item List all columns in server

• Barry J. Stratta

  Mr or Mrs. 500

  Points: 557

  More actions

  September 30, 2010 at 7:23 am

  #1229601

  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

• Bryan.Bowden

  Grasshopper

  Points: 17

  More actions

  September 30, 2010 at 9:11 am

  #1229693

  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

• Barry J. Stratta

  Mr or Mrs. 500

  Points: 557

  More actions

  September 30, 2010 at 12:16 pm

  #1229795

  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

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 18, 2016 at 7:18 am

  #1879867

  Thanks for the script.