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
 SQL Server Administration (2000)
 Object search / repository 2005?

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-05-19 : 04:37:11
Hi all,

I have just started a new job and the databases I'm working with are quite large. Not so much data actually, but the tables are wide as he** some of them having over 200 fields. Is there any way I can easily search through a database for a specific field? I was hoping there was sort of an "object search" in MgmtStudio but I can't seem to find one...

And another thing: do you guys have any tips on how to make use of a code repository in MgmtStudio? Usually when I code I use the same methods for debugging and quite often I see myself write the same code over and over again because I don't save this anywhere. I could always have a folder with sql-files but it would be so much easier to just hit Ctrl + Alt + * or something when I wanted to write a "select top 10 * from table"... What do you guys do? Do you have any methods that serve you well and could recomend to others?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-19 : 04:57:23
object search like...

select * from information_schema.columns
where column_name='column_name'
and table_name='table_name'

the is views are still there with additional sys views as well...

--------------------
keeping it simple...
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-05-19 : 05:09:33
Yes exactly! Hm...now if I can find a handy way to save this in a code repository of some sort I'd be all set I wonder why they didn't add intellisense in the management studio...would make coding sooo much easier...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-19 : 08:43:45
i can answer that
i asked Don Vilen SQL server program maganer the same question.
his answer:
We leave this to 3rd party tools.

they rather spent time on doing other cool stuff.

promptsql is one of the easieast to use and it's great.

as for code repository... i don't think that's enabled in SSMS.

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-05-19 : 08:56:57
Wow, an answer from The Source of all Sources hehe But it's really too bad when they have intellisense for basically all other languages in Visual Studio. I can't imagine it beeing that hard to add either, at least not for tables/columns. I used SQL IDE from Imceda for a few years and even though it was kinda slow and didn't work exactly like it should all the time, I loved it. Maybe I have to start using it again...600$ isn't that bad if it makes your coding more efficient.

Avout the code repository; I actually found something pretty close to what I was looking for! I found something called the TemplateExplorer (Ctrl + Alt + T) that lets you add custom templates. Not 100% what I was looking for but pretty close

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-19 : 09:11:26
well as it was explained to me it is that hard.
when you do
select ... <- here the program can't know what you want
from ... <- so you have to first specify this and then go back etc

emmmm... promptSQL costs $25... although redgate bought it a month ago.
man that guy must've made some serious money

i tried to force QA to open a file with begin tran ... commit on start and when you click
new file...
succeded only to force it on start.

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-05-19 : 10:22:59
The way the imceda-app worked was that you had to make aliases for the tables (which I always do anyway) or make qualified names for the tables. So when you typed "." your options popped up. The bad part is that you would have to type the FROM-stuff before you write the SELECT-stuff.

This promptSQL you mentioned, is it a plugin or something to SSMS?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-19 : 10:58:22
either
http://www.promptsql.com/

ir through the add on top of this page.

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

datagod
Starting Member

37 Posts

Posted - 2006-05-19 : 13:34:58
quote:
Originally posted by Lumbago

Hi all,

Is there any way I can easily search through a database for a specific field?



There sure is! sp_FindReferences (works in 2000, not sure about 2005)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65666
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-05-22 : 09:39:29
Spirit: OMG! I'm SOOOOOOOOOOO gonna buy this product! These must be the best 50 bux I'll ever spend I have to figure out some stuff here first but it even has the snippet-thingy I was looking for...alot better than the TemplateExplorer I suggested earlier. You made my day once more spirit, I believe I offered you free accomodation on my couch in Oslo once before...the offer still stands!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-05-22 : 09:44:09
and datagod; your procedure looks great...I'll have to look in to it :)

501 posts, yeehaaa!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-23 : 02:57:06
i just might take you up on that Lumbago.

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2006-12-19 : 14:21:07
sorry guys, I found that by running this query, I can find the location of the indexes. i.e. which filegroups they are on. But Could not come up with a similar query for finding out where all the tables were located.

select si.name,DS.NAME AS FILE_GROUP_NAME from sysindexes si
JOIN SYSOBJECTS SO ON SO.NAME=SI.NAME
JOIN SYS.DATA_SPACES DS ON SI.GROUPID=DS.DATA_SPACE_ID
order by groupid,name

That is why I wanted to find where the data_space_id or Groupid columns reside in SQL2005 to change the above query for tables. any insight might help
Go to Top of Page
   

- Advertisement -