Find database tables and coloumns

Question

Find database tables and coloumns

sampathsoft

SSC Eights!

Points: 855
More actions
March 25, 2008 at 10:27 pm

#71291

Hello,
can anybody help me to find dynamically ( i tried to mean i have to write storedprocedure to find all tables) all tables and check their coloumn.
My purpose is want to check all table coloumns and if my checking coloumn is inside some table, then i want to update that table column
Ex- if my database has 500 tables i want to check one by one that tables and their coloumns.
thankyou

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

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

You'll need to write some dynamic SQL that uses the Information_Schema views... both are in Books Online. This will likely require a loop of some sort... most prefer a cursor...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

goodguy SSCertifiable Points: 5456 More actions · Answer 2

Pardon me if I sound a bit junior, but depending on why it is required, wouldn't it be a good idea to simply generate a table script to get all the tables and columns in a database?

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

No need...

SELECT Table_Name, Column_Name FROM Information_Schema.Columns

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Kinda of late to get into this subject but from various SSC contributors (whom I thank) patched together this procedure to tell you more than you wanted to know about SQL 2000 tables.

CREATE PROCEDURE dbo.UDP_Table_Definitions

AS

Declare @default VARCHAR(128)

Declare @tname VARCHAR(128)

Declare @cname VARCHAR(128)

Declare @dtype VARCHAR(30)

Declare @dlength INT

Declare @sstatus CHAR(3)

Declare @xcomp CHAR(3)

Declare @xdef INT

SET NOCOUNT ON

Create Table #UDT_TDefs

(

TName VARCHAR(128),

CName VARCHAR(128),

DType VARCHAR(30),

Dlength INT,

Sstatus CHAR(3),

xComp CHAR(3),

xDef VarChar(30)

)

DECLARE table_cursor CURSOR fast_forward FOR

SELECT so.name, sc.name, st.name, sc.length, sc.Status, sc.iscomputed, sc.cdefault

From Sysobjects so, syscolumns sc, systypes st

where so.xtype = 'U' and sc.id = so.id and sc.xtype = st.xusertype AND NOT so.name = 'dtproperties'

Open Table_Cursor

Fetch Next from Table_Cursor into @tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @xdef

While @@Fetch_Status = 0

Begin

Set @Default = ' '

If @xdef > 0

Set @Default = (Select text from syscomments where @xdef = id)

Insert Into #UDT_TDefs

(TName, CName, DType, Dlength, Sstatus, xComp, xDef)

Values (@tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @Default )

Fetch Next from Table_Cursor into @tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @xdef

End

Close Table_Cursor

Deallocate Table_Cursor

Select

tname As 'Table Name', cname as 'Column Name', DType as 'Data Type', dlength as 'Len', sStatus as 'NP1', xComp as 'NP2', xdef as 'Default',

Case tname When 'int' then Cast(dlength as Char(6)) Else ' ' end As 'Precision',

Case sStatus When 8 then 'Yes' When 24 then 'Yes' When 56 then 'Yes' Else ' ' end As 'Allow Nulls',

Case sStatus When 128 then 'Yes' Else ' ' end As 'Identity',

Case xcomp When 1 then 'Yes' Else ' ' end As 'Computed'

From #UDT_TDefs

Order by tname

Drop table #UDT_TDefs

Run command - dbo.UDP_Table_Definitions

Returns the following:

Table Name,Column Name,Data Type,Len,NP1,NP2,Default,Precision,Allow Nulls,Identity,Computed

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]