List all columns in server

  • shanu.hoosen

    SSCrazy

    Points: 2227

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

  • Barry J. Stratta

    Mr or Mrs. 500

    Points: 557

    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

    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

    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

    Thanks for the script.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply