Welcome to the Lounge

exec sp_help '<your table name>'

sp_helptable '<your table name>'

Southmountain wrote:

now I have a big table in a database, what is the best way to get understanding of this table quickly?

Southmountain wrote:

it has hundreds of fields and I only know some keys.

Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Eddy Vluggen wrote:

DROP TABLE would be the best start;

jschell wrote:

I think there was something like 300 or 400 columns

jschell wrote:

Probably could not have dropped it. It held credit card transaction data.

Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Eddy Vluggen wrote:

That's why I stopped visiting the hospital. I don't wont to die by VB6

On Error Resume Next Life

-- Build the View of the Maximiser data that shows what we want and store it

SELECT     
CASE 
	WHEN c.Record_Type = 1 THEN c.Name
	WHEN c.Record_Type = 31 THEN d.Name + ' - ' + c.First_Name + ' ' + c.Name
	WHEN c.Record_Type = 2 AND len(c.Firm) > 0 THEN c.Firm
	WHEN c.Record_Type = 2 AND len(c.Firm) < 1 THEN c.First_Name + ' ' + c.Name
	WHEN c.Record_Type = 32 THEN 
	(
		CASE 
			WHEN len(d.Firm) > 0 THEN d.Firm + ' - ' + c.First_Name + ' ' + c.Name
			WHEN len(d.Firm) < 1 THEN d.First_Name + ' ' + d.Name + ' - ' + c.First_Name + ' ' + c.Name 
		END
	)
	ELSE c.Name
END AS Company,
CASE
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.Address_Line_1 
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Address_Line_1
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN f.Address_Line_1
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.Address_Line_1
	ELSE c.Address_Line_1
END AS Address_1,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.Address_Line_2 
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Address_Line_2
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN f.Address_Line_2
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.Address_Line_2
	ELSE  c.Address_Line_2
END AS Address_2,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.City
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.City
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN f.City
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.City
	ELSE  c.City
END AS City,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN e.State_Province
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.State_Province
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN  f.State_Province
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.State_Province
	ELSE  c.State_Province
END AS State,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN  e.Zip_Code
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Zip_Code
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN  f.Zip_Code
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN  g.Zip_Code
	ELSE  c.Zip_Code
END AS Zip,
CASE 
	WHEN c.Address_Id > 0 AND c.Record_Type IN (1, 31) THEN  e.Country
	WHEN c.Address_Id < 1 AND c.Record_Type = 31 THEN  g.Country
	WHEN c.Address_Id > 0 AND c.Record_Type IN (2, 32) THEN  f.Country
	WHEN c.Address_Id < 1 AND c.Record_Type = 32 THEN      g.Country
	ELSE c.Country
END AS Country,
CASE 
	WHEN n.Type = 0 THEN  'Manual Note'
	WHEN n.Type = 1 THEN  'Mail - Out' 
	WHEN n.Type = 2 THEN  'Phone Call'
	WHEN n.Type = 3 THEN  'Timed Note'
	WHEN n.Type = 4 THEN  'Transfer'
	WHEN n.Type = 5 THEN  'Task'
	WHEN n.Type = 6 THEN  'Reserved' 
	WHEN n.Type = 7 THEN  'Reserved'
	WHEN n.Type = 8 THEN  'Opportunity'
	WHEN n.Type = 12 THEN 'Customer Service'
	ELSE  'Unknown'
END AS Activity_Type,
n.DateCol, n.TextCol, n.Owner_Id, n.Client_Id, n.Contact_Number, n.Note_Type,
'    ' AS sndex 
INTO BookerNotes
FROM
	dbo.AMGR_Client_Tbl AS c 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS d ON c.Client_Id = d.Client_Id AND d.Contact_Number = 0 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS e ON c.Client_Id = e.Client_Id AND c.Address_Id = e.Contact_Number 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS f ON c.Client_Id = f.Client_Id AND c.Address_Id = f.Contact_Number 
	LEFT OUTER JOIN dbo.AMGR_Client_Tbl AS g ON c.Client_Id = g.Client_Id AND g.Contact_Number = 0 
	RIGHT OUTER JOIN dbo.AMGR_Notes_Tbl AS n ON c.Client_Id = n.Client_Id AND c.Contact_Number = n.Contact_Number
WHERE c.Record_Type IN (1, 2, 31, 32)
GO

Southmountain wrote:

it has hundreds of fields

Southmountain wrote:

what is the best way to get understanding of this table quickly?

Southmountain wrote:

what is the best way to get understanding of this table quickly?