How to get the Script for an object using query Analyser?

Hi,

I wanted to know how to get the script for an object using query analyser is there a system procedure to get the script. how does the enterprise manager generate the script?

Check out sp_helpText in BOL.

Cheers,
Loonysan

|||

sp_helptext is used for procedure,view,trigger etc

what if i want to get the scrip of a table? or job like what we get in the enterprise manage using the GENERATE sCRIPT option

|||

sp_help describes database objects. It might not be in the exact format you need it, but you should be able to manipulate the stored procedure in the way you need it. I also did a query like the following and this gave me information like column name, datatype and length of a table. There are other columns in the syscolumns table that specifiy collation and if it can contain a null (isnullable field in syscolumns).

select c.[name],t.[name],c.length
from sysobjects o
join syscolumns c
on o.id = c.id
join systypes t
on c.xtype = t.xtype
where o.[name] = 'nissannow_orders'

Results:

CreateDate datetime 8
OrderDate datetime 8
ExportSubmissionDate datetime 8
ModifiedDate datetime 8
OrderID int 4
CustomerID int 4
DealerIDDealerVisited int 4
CreatedBy varchar 50
ShipPrimaryPhone varchar 20
ShipEmailAddress varchar 50
ModifiedBy varchar 50
ShipCity varchar 50
ShipStateProvince varchar 50
ShipZip varchar 20
ShipFirstName varchar 100
ShipLastName varchar 100
ShipAddress1 varchar 100

Let me know if you need any more assistance with this.

|||There is no easy way to do this in TSQL. You need to either write a SP that does the work using the metadata in the system tables or use the built-in client API like DMO/SMO. You can write VB scripts for example that uses DMO to generate scripts or DTS/SSIS tasks.

How to get SQL Server Management Studio to automatically connect upon startup?

Hi all,
2000's Enterprise Manager was smart enough to automatically connect to my
last database when it was launched.
Somehow I can't find out how to turn on this extremely useful feature in
2005's Management Studio. Can anyone tell me the secret?
Thanks,
C17
> 2000's Enterprise Manager was smart enough to automatically connect to my
> last database when it was launched.
> Somehow I can't find out how to turn on this extremely useful feature in
> 2005's Management Studio. Can anyone tell me the secret?
None of these things are "secrets." You can add command line parameters and
create shortcuts for different servers. Here are the possible startup
parameters for Management Studio (sqlwb.exe):
Usage:
sqlwb.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P
password] [-E] [file_name[, file_name]] [/?]
[-S The name of the SQL Server instance to which to connect]
[-d The name of the SQL Server database to which to connect]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login with which to connect]
[-P The password associated with the login]
[file_name[, file_name]] names of files to load
[-nosplash] Supress splash screen
[/?] Displays this usage information
However, this won't automatically open Object Explorer. (Same was true in
Query Analyzer, IIRC.)
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Thank you, that works great!!
It's still annoying that one can't set a default via the UI, though. From
reading the Feedback pages, and the MSFT responses, it seems as though
everyone at MSFT thinks that we're all using Management Studio from inside
of VS, and so the response to a lot of these usability requests is
basically,
"Well, you can do that from inside of VS." I think they forget that a lot
of us use it for management, not development!
Thanks again,
C17
|||You maybe are misunderstanding the posts from Microsoft. You
don't use Management Studio from inside Visual Studio. You
use Management Studio OR Visual Studio.
What posts are you referring too? You are always free to
offer product suggestions.
-Sue
On Thu, 14 Jun 2007 14:41:07 -0400, "C17"
<c17@.community.nospam> wrote:

>Thank you, that works great!!
>It's still annoying that one can't set a default via the UI, though. From
>reading the Feedback pages, and the MSFT responses, it seems as though
>everyone at MSFT thinks that we're all using Management Studio from inside
>of VS, and so the response to a lot of these usability requests is
>basically,
>"Well, you can do that from inside of VS." I think they forget that a lot
>of us use it for management, not development!
>Thanks again,
>C17
>

How to get size of a table

Hello,

I want to get size of a particular table. I have used the wizard provided by SQL Server Enterprise Manager. But it is giving wrong size. If the data of some cells has been deleted or some rows are deleted. It shows the same size which it shows for the first time. I have also used sp_spaceused but is showing the same behaviour as mentioned above. I deleted some rows from the table but is showing the same size.I want to know the size exactly, so that i can come to know that some rows has been deleted or data is corrupted. Please tell me the solution as early as possible.

Haider Ali Khan

sp_spaceused()

How to get list (text) of all tables and columns?

Is there a way using MS SQL Server and Enterprise Manager to get a text
document (or perhaps even a Word document) listing all table names,
column names, etc of a database?

--
Sugapablo
-----------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal"Sugapablo" <russREMOVE@.sugapablo.com> wrote in message
news:vmu7b03j5uos68@.corp.supernews.com...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
> --
> Sugapablo
> -----------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal

It would probably be easier to use Query Analyzer instead, and get the
details you need from the INFORMATION_SCHEMA views (assuming you have SQL7
or 2000), eg.:

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION

If it's a one-off task, then you can just cut and paste the results, but if
you need to do it regularly, then you could consider using SQLDMO from a
client application. That way you can create the whole Word document using VB
or something similar.

Simon|||Sugapablo <russREMOVE@.sugapablo.com> wrote in message news:<vmu7b03j5uos68@.corp.supernews.com>...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?

Hello,

To get a list of user tables use this query:

select name from ssysobjects where type = 'u'

And for a list of columns related for all objects (tables,views,sps)

select name, object_name(id) from sysindexes

The results could be pushed to a text document or any other ODBC
compliant datasource using DTS.

Regards,
-Manoj

How to get executing sql and pids via sql from a command line....

To all,
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--sean
Hello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net ...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>

How to get executing sql and pids via sql from a command line....

To all,
We are currently in the process of migrating from postgresql to SQL Server
2000 Enterprise for our data warehouse. There is a nice little sql call in
postgresql that allows me to dump the currently running queries on an
instance.
Select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query
| query_start
--+--+--+--+--+--
----
----
----
--+--
17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.465191-04
17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.351345-04
17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.475562-04
17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.486901-04
17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:40.497608-04
17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:59:00.59342-04
17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.366206-04
17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.381277-04
17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
| 2004-10-04 21:58:25.395722-04
17142 | tripmaster | 11820 | 100 | tripmaster | select * from
pg_stat_activity;
| 2004-10-04 21:59:57.65278-04
pg_stat_activity is a system table used for gathering stats.
My question is there an equivalent sql statement or group of statements that
I could execute to get the same sort of info from sql server?
Thanks.
--seanHello ,
Look into the sysprocesses table in master database. But it will not show
the Queries . To get the queries you could use the function fn_getsql
(introduced in sql 2000 sp3). See books online for the usage of function.
Thanks
Hari
MCDBA
"Sean Shanny" <shannyconsulting@.earthlink.net> wrote in message
news:BD87780A.1A8C3%shannyconsulting@.earthlink.net...
> To all,
> We are currently in the process of migrating from postgresql to SQL Server
> 2000 Enterprise for our data warehouse. There is a nice little sql call
> in
> postgresql that allows me to dump the currently running queries on an
> instance.
> Select * from pg_stat_activity;
> datid | datname | procpid | usesysid | usename |
> current_query
> | query_start
> --+--+--+--+--+--
> ----
> ----
> ----
> --+--
> 17142 | tripmaster | 11815 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.465191-04
> 17142 | tripmaster | 11811 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.351345-04
> 17142 | tripmaster | 11816 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.475562-04
> 17142 | tripmaster | 11722 | 100 | tripmaster | SELECT subage,
> COUNT(DISTINCT(t1.session_key)), COUNT(DISTINCT(t1.userid_key)) FROM
> f_pageviews t1 JOIN segmented_sub t0 ON (t1.userid_key = t0.id) WHERE
> t1.date_key BETWEEN 640 AND 641 AND t1.newsletterid_key NOT IN (SELECT
> newsletterid FROM t_newsconten | 2004-10-04 21:58:24.613488-04
> 17142 | tripmaster | 11817 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.486901-04
> 17142 | tripmaster | 11818 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:40.497608-04
> 17142 | tripmaster | 11819 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:59:00.59342-04
> 17142 | tripmaster | 11812 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.366206-04
> 17142 | tripmaster | 11813 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.381277-04
> 17142 | tripmaster | 11814 | 100 | tripmaster | <IDLE>
> | 2004-10-04 21:58:25.395722-04
> 17142 | tripmaster | 11820 | 100 | tripmaster | select * from
> pg_stat_activity;
> | 2004-10-04 21:59:57.65278-04
>
> pg_stat_activity is a system table used for gathering stats.
> My question is there an equivalent sql statement or group of statements
> that
> I could execute to get the same sort of info from sql server?
> Thanks.
> --sean
>
>

How to get Enterprise Mgr on pc?

I have installed a 64-bit SQL Server 2005 standard edition onto my database
server. I now want to install the client onto my pc.
How do I install onto client pc?
Thanks!
You pop in the CD or DVD that you used to install the server components and
select the "Server components, tools, Books Online, and samples"
installation.
Click through until you can select the components to install.
When you are on that window choose Workstation Components, Books Online, and
development tools checkbox.
Keith Kratochvil
"kwl88" <kwl88@.discussions.microsoft.com> wrote in message
news:3CEC8CD5-9F7B-437E-8B4F-968C3E5A62C1@.microsoft.com...
>I have installed a 64-bit SQL Server 2005 standard edition onto my database
> server. I now want to install the client onto my pc.
> How do I install onto client pc?
> Thanks!
|||Keith:
You would be incorrect!
I recieve the following error message:
"Error: %1 is not a valid Win32 application"
It appears that I need to install a 32bit SQL Server Client. OR is there
some workaround ou there?
Thanks!
"Keith Kratochvil" wrote:

> You pop in the CD or DVD that you used to install the server components and
> select the "Server components, tools, Books Online, and samples"
> installation.
> Click through until you can select the components to install.
> When you are on that window choose Workstation Components, Books Online, and
> development tools checkbox.
> --
> Keith Kratochvil
>
> "kwl88" <kwl88@.discussions.microsoft.com> wrote in message
> news:3CEC8CD5-9F7B-437E-8B4F-968C3E5A62C1@.microsoft.com...
>
>
|||Just install the 32 bit client onto the client computer.
I would download the 32 bit Windows Server 2005 Evaluation and just install
the client tools.
Both the 32 and 64 bit management studios will connect to each type of SQL
Server.
http://www.microsoft.com/windowsserv.../overview.mspx
"While compatible with 32-bit applications and existing deployment and
management tools, Windows XP Professional x64 Edition provides more
efficient processing of extremely large amounts of data, supporting up to 16
terabytes of virtual memory."
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"kwl88" <kwl88@.discussions.microsoft.com> wrote in message
news:83D54961-0335-4642-8350-B14D9D78290B@.microsoft.com...[vbcol=seagreen]
> Keith:
> You would be incorrect!
> I recieve the following error message:
> "Error: %1 is not a valid Win32 application"
> It appears that I need to install a 32bit SQL Server Client. OR is there
> some workaround ou there?
> Thanks!
> "Keith Kratochvil" wrote:
|||The client tools, SSMS, SSIS, etc., are 32 bit applications, and are best
executed from 32 bit environments. They will operate from a 64 bit
environment using WOW, but at a performance penalty.
See: http://support.microsoft.com/kb/906892/
The 32 bit tools are, as far as I recall, located in a folder on the 64 bit
product disk. You may have to search around a bit.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"kwl88" <kwl88@.discussions.microsoft.com> wrote in message
news:83D54961-0335-4642-8350-B14D9D78290B@.microsoft.com...[vbcol=seagreen]
> Keith:
> You would be incorrect!
> I recieve the following error message:
> "Error: %1 is not a valid Win32 application"
> It appears that I need to install a 32bit SQL Server Client. OR is there
> some workaround ou there?
> Thanks!
> "Keith Kratochvil" wrote:
|||To clarify:
So I download and install for free the 32 bit Win server 2003 Eval copy and
install ONLY the client tools - Correct?
AND So do I eventually have to pay for these client tools? AND what about
the 4 CALS Licenses that I paid for? Will MS give me a refund for them?
What if I upgrade my .Net framework from 1.1 to 2.0 and then try to install
from my 64bit SQL Server install DVD might that work better?
Is there any documentation on this error on the internet? GOOD documentation?
Thanks!
Thanks!
"Warren Brunk" wrote:

> Just install the 32 bit client onto the client computer.
> I would download the 32 bit Windows Server 2005 Evaluation and just install
> the client tools.
> Both the 32 and 64 bit management studios will connect to each type of SQL
> Server.
> http://www.microsoft.com/windowsserv.../overview.mspx
> "While compatible with 32-bit applications and existing deployment and
> management tools, Windows XP Professional x64 Edition provides more
> efficient processing of extremely large amounts of data, supporting up to 16
> terabytes of virtual memory."
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "kwl88" <kwl88@.discussions.microsoft.com> wrote in message
> news:83D54961-0335-4642-8350-B14D9D78290B@.microsoft.com...
>
>
|||It makes sense that the 32-bit client tools are on the 64-bit disc, as Arnie
mentioned.
I thought that the minimum # of CALs that could be acquired when you
purchase SQL Server was 5. How did you only purchase 4?
Regarding the 4 CALs - are you the only person/entity/computer connecting to
the database server? If so, you purchased too many licenses anyway...
Keith Kratochvil
"kwl88" <kwl88@.discussions.microsoft.com> wrote in message
news:77F66625-2146-465E-8137-5A340FFB2C7A@.microsoft.com...
> To clarify:
> So I download and install for free the 32 bit Win server 2003 Eval copy
> and
> install ONLY the client tools - Correct?
> AND So do I eventually have to pay for these client tools? AND what about
> the 4 CALS Licenses that I paid for? Will MS give me a refund for them?
> What if I upgrade my .Net framework from 1.1 to 2.0 and then try to
> install
> from my 64bit SQL Server install DVD might that work better?
> Is there any documentation on this error on the internet? GOOD
> documentation?
> Thanks!
> Thanks!