Lots of developers write code in flash with the help of intellisense. But when coding t-sql is scripts, there is no built-in intellisense on query analyzer or SSMS. You will have to use a 3rd party tool to have intellisense on your query analyzer.
But if you can't find 3rd party intellisense tool usefull 'cause some tools eats all your memories or something, there's a lot of cool stuff in query analyzer or SSMS, like using the object browser and dragging the column folder of the table to list all the columns seperated by a comma.
And also you can use a keyboard shortcut.
In Tools>Customize on QA or Tools>Options>Environment>Keyboard on SSMS, you can write a sql statement and assign it on a shortcut ket that will execute whenever you press the shortcut key.
For example:
Input the statement "
select [name]
from sysobjects where xtype =
'S'" on CTRL+5 and it will list all the stored procedure when you press CTRL+5.
The cool part is, when you highlight a word on the editor, it will serve as a parameter at the sql statement you write on a shortcut key. Like when you put
sp_helptext on one of the shortcut keys, you can just highlight a SP or UDF or View name on the editor and press the shortcut key to view the code.
here's the sql statements I used on the shortcuts.
CTRL+3 -- list all the tables
select TableName=o.name, [Rows]=
max(i.[rows]),o.id,(
select count(*)
from syscolumns where id=o.id)
as ColCount
from sysobjects o
join sysindexes i
on o.id=i.id
where xtype=
'u' and OBJECTPROPERTY(o.id, N
'IsUserTable')=1
group by o.name, o.id
order by TableName
CTRL+4 -- list all the views
select Table_Name,
'VIEW' as Type
from INFORMATION_SCHEMA.Tables
where Table_type =
'VIEW' order by Table_Type
CTRL+5 -- list all the SP
select [name], 'Stored Procedure' as Type from sysobjects where xtype = 'P' and status >= 0 order by type desc, [name] CTRL+6 -- list all the UDF
select [name], 'Function' as Type from sysobjects where xtype in ('TF','FN','IF') and status >= 0 order by type desc, [name] CTRL+7 -- for viewing the code of SP, UDF, View (requires parameter {highlighted text on editor})
sp_helptext CTRL+8 -- to list all the column of a table (requires tablename parameter {highlighted text on editor})
sp_MShelpcolumns I also create a SP:
CREATE PROCEDURE _sp_Select_From
@objTable
varchar(255)
, @Order1
varchar(255) = 1
, @Order2
varchar(255) = 2
, @Order3
varchar(255) = 3
as
exec (
'select * from ' + @objTable +
' order by ' + @Order1 +
', ' + @Order2 +
', ' + @Order3)
and assign it on CTRL+9 to show the data on a table.
And I created a SP:
CREATE PROCEDURE _sp_PrintColumns
@ObjName
varchar(8000)
as set nocount on declare @TotalParams
int, @cntr
int, @ObjID
int, @ColName
varchar(50), @ColValue
varchar(50), @ColTypeID
int, @ColType
varchar(50), @TempColumnName
varchar(50)
set @ObjID = (
select [id]
from [dbo].
sysobjects where [name] = @ObjName)
if @ObjID
is NULL or @ObjID =
'' begin select 'object not found' return
end set @TempColumnName =
'' set @TotalParams = (
select top 1 colid
from [dbo].
syscolumns where [id] = @ObjID
order by colid
desc)
set @cntr = 1
print '--' + @ObjName
while @cntr <= @TotalParams
begin set @TempColumnName = @ColName
select top 1 @ColName = [name]
, @ColTypeID = xtype
from [dbo].
syscolumns where [id] = @ObjID and colid = @cntr
set @ColType = (
select distinct top 1 [Type_Name]
from master.dbo.spt_datatype_info
where ss_dtype = @ColTypeID)
if @TempColumnName<> ''
print ', ' + @ColName
else
print @ColName
set @cntr = @cntr + 1
endand assign it on CTRL+0 to list all the columns of a table or parameters of SP and UDF seperated by a newline and a comma.
And it helps me a lot and makes me more productive.
Posted
09-27-2006 3:32 PM
by
clintz