Text fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Text fields

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-07-14 : 04:13:39

Can someone please point me in the right direction to write a script. I want to identify all the fields in a couple of tables in one database that are of type string (i.e. all the various char types, or any other type that could hold a string). I suspect that it is in one of the system databases somewhere but don't know where to start to look

many thanks

steve

Steve no function beer well without

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-07-14 : 04:23:36

SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'MyTable'

Enjoy

Damian

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 07:57:15

Sorry, don't know how to do it with INFORMATION_SCHEMA, too old :-(


SELECT	[Table] = O.name, 
	[Column] = C.name,
	[TypeID] = C.xtype,
	[Type] = T.name
FROM	dbo.syscolumns C
	JOIN dbo.sysobjects O
		 ON O.ID = C.ID
		AND O.type = 'U'
	JOIN dbo.systypes T
		ON T.xtype = C.xtype
WHERE	   T.name LIKE '%char'
	OR T.name LIKE '%text'

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-07-14 : 12:07:34

Thanks to both, Kristen I think your's does exactly what I was hoping

cheers

steve

Steve no function beer well without

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 12:49:10

Yeah, well I think Merkin thought this was a homework thread so didn't want to give the whole game away, but I know how busy you REALLY are ... :)

Kristen

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-07-14 : 20:56:30

LOL

No I didn't think that at all, I just knew Steve could figure out the rest from there.
I like using the systables for stuff like this, but tire of typing out the "MS may change this at any time" disclaimers

Damian

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 21:25:14

Better idea: Don't tell them and then change SQLTeam to be a paid-for service when MS no long support sysXXX tables