September 29, 2010 at 11:52 pm
Comments posted to this topic are about the item List all columns in server
September 30, 2010 at 7:23 am
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
September 30, 2010 at 9:11 am
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
September 30, 2010 at 12:16 pm
Bryan.Bowden (9/30/2010)
BarryThank 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
May 18, 2016 at 7:18 am
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply