Documenting Existing Stored Procedures
Sep 8, 2004Does anyone have any scripts, or know of any tools, that can scan through all of the SPs that I have inherited, documenting details?
Thx
ADVERTISEMENT
Existing Stored Procedures
Dec 13, 2007hi,
is there someway I can use to copy an existing stored procedures to another database?
Thanks!
ANSI_NULLs Settings For Existing Stored Procedures
May 8, 2008
As noted on the msdn the ANSI_NULLS for a stored procedure are set at time off creation (http://msdn.microsoft.com/en-us/library/aa259229(SQL.80).aspx).
Using:
- SQL server 2000
My question is; How do i get the details of a sp and display the ANSI_NULLS setting for it.
("select * from information_schema.routines" doesnt shows this information)
Is this possible in SQL 2000 (or 2005)?
T-SQL (SS2K8) :: Search Based On Table Name And Add Code To Existing Stored Procedures
Jun 30, 2014Below is sample SQL:
SQL_1: Creates a database
SQL_2: Creates 2 stored procedures
Now, I need to search database SP`s for Table2 t2 ON t2.CID = t1.CID and ALTER them with Table2 t2 ON t2.CID = t1.CID.
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'SQL_1:
USE [master]
GO
[code]...
SQL Server 2008 :: Find Not Existing Calls In Stored Procedures Or Functions
Mar 17, 2015How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
View 7 Replies View RelatedOracle Stored Procedures VERSUS SQL Server Stored Procedures
Jul 23, 2005I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedDB Engine :: In-Memory OLTP Use With Existing Tables / Index / Procedures
Nov 10, 20151. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index. do I need and code changes for application and how to store tables /indexes in OLTP memory
Assume table index may have primary key index as well.
2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.
3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.
Stored Procedures 2005 Vs Stored Procedures 2000
Sep 30, 2006Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
All My Stored Procedures Are Getting Created As System Procedures!
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
Documenting
Mar 7, 2006Hi
I need some templates or document examples to create a Disaster Recovery Planning document for SQL Server.
Please help.
Regards
DTS Documenting A Package
May 27, 1999How do you document a DTS package? I cannot figure out how to print it, with all its parameters, code etc...
Where is it all stored?
Would be very useful.
Documenting SQL Tables
Apr 7, 1999Hi Folks,
I am new to this list server and have a dumb question. Does anybody know of any software that will document table/attributes very similiar to sp_help?? Basically, what I am looking for is some fancy documentor for tables very much like the one with access.
Thanks...
Documenting A Database
Apr 15, 1999Is there any way to print a report with column name, datatype, size, etc in a report under SQL Server 6.5. I am looking for something
like the Documenter which is available with Access 97. Karen Suenram
Documenting My Database
Nov 7, 2006Hi folks,
I'm so sure this is a clueless newbie question, I'm embarrassed to be asking it. The short version of my question is, "What's the best path to generating printed documentation of my SQL 2005 database?" The long version follows...
I've just completed the first draft of my new SQL database design (using Management Studio). We were previously storing our data in seven (yes, seven) separate Access databases, now I have everything set up quite nicely in one SQL db, all nice and relational and with proper field sizes and naming conventions, etc. It's all beautiful, but now I need to document it.
Why wasn't it documented first and then built? Let's just say I inherited this task in mid-stream with a looming deadline. Yes, I would have liked to follow a different workflow, but it is what it is and I am where I am.
So I can go through every table and type up everything in Word, but I'm hoping there's some feature in Management Studio that will give me a head start. I read the Help file under "documenting databases," and it says you can output the metadata as and XMLA script. Sadly, it doesn't actually tell you how to do this, which is rather unhelpful.
I'd like to end up with a document that list all my tables, with field definitions, indexes, keys, and triggers. Is there an easier way to get there than going table-to-table and typing it all out?
Thanks,
Ron Moses
ConEst Software Systems
Documenting DTS Packages
Feb 27, 2007Is there a way to quickly document all tables and views a DTS package uses? Is there any system table/view which will hold this information?
For one of our projects we need to document these objects so that the client could remove unused objects from the db. There are 20 + DTS packages to go through.
Any help/suggestion would be greatly appreciated.
Documenting SQL Servers/databases
Mar 27, 2001I'm fairly new to a DBA role, I've been tasked with documenting all the SQL servers/databases. Is there anywhere on the Web where I could find a standard list of what needs to be documented. Any help/advice would be appreciated.
View 1 Replies View RelatedHas Anyone Heard Of This IT Documenting Software?
Jul 28, 2006(Spam Removed)
View 6 Replies View RelatedDocumenting Large Numbers Of SPs
Jul 27, 2007Hi All,
I have a large number of SPs that I would like to be able to document and provide this documentation to prospective clients. That is, provide them enough information without giving them the source code for the procedures.
I have found that all the parameters are in the sys.parameters table.
But I was wondering. Are the fields that are sent back out of an SP captured and recorded somewhere in the SQL Server catalog?
Is there an easy way to find out what fields are coming out of an SP?
Thanks in Advance
Peter
Documenting SQL 2005 Packages
Apr 3, 2006Hi all
I would like to select transforms in a package and paste them as jpeg or similar into word or powerpoint in order to allow me to create a separate set of documentation.
However, whenever I copy some transformations and jump to word, the copy buffer is empty.
Any suggestions?
Thanks
How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures
Apr 29, 2008How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Documenting A SQL Server 2000 Database
Dec 24, 2003I am looking for an easy way to document the specifications of my tables, views, etc. In MS Access, one could use the "Documenter" utility to print out field names, data types, field sizes, and comments. Does SQL Server 2000 have a similar utility?
If not...
1. Why not? This would seem to be very useful.
2. What is the recommended way to document database specifications?
My ultimate goal is to develop an administration manual for our database.
Please note: I looked at the database designer diagram utility which is great for visually representing the relationships between tables. However, I am looking for a utility that will print out the field names, data types, field sizes and comments related to the fields.
Any help would be appreciated.
Thanks :)
Documenting Tables/fields - Utility?
Aug 16, 2006As part of a project documentation I want to list out the fields of all the tables to say Excel to plug in a description of the field and other notes.
Is there a tool in EM that would facilitate? Or, is there a utility that would work or a SQL script? Any recommendations is greatly appreciated.
Thanks,
Peter
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Jun 13, 2007Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedHow To Save Stored Procedure To NON System Stored Procedures - Or My Database
May 13, 2008Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
Dataset With Existing Stored Procedure
Mar 11, 2008I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts?
Thanks in advance,
N
How To Modify An Existing Stored Procedure?
Apr 16, 2008Hi all,
I have an Existing Stored Procedure on the database, and I want to modify it. However, when I changed it and saved it with the same name, the value,say 20 here I changed was not upgraded to the new value (I wanna it be 30). so anyone know how to solve that? Thanks in advance.
ALTER PROCEDURE [dbo].[spr_getCompanyId]( @companyname as varchar(20), @companykeyword as varchar(20) //i want to change 20 to 30
)
Modify Existing Stored Procedure??
Dec 2, 2005I have a stored procedure I created in SQL Server 2000 enterprise manager which I would like to modify in SQL Server 2005 Express Management Studio. When I right click on the stored proc and select "Modify", the code opens - however any attempt to save creates a local .SQL file. How can I save these changes to the stored procedure on the server?
View 1 Replies View RelatedHow To Encrypt All Existing Stored Procedure?
Aug 18, 2006I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.
But how about if I want encrypt existing stored procedures?
Which command should I use ?
Stored Procedure Being Saved In System Stored Procedures
Apr 7, 2006We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
SSIS And Stored Procedures Results Stored In #Tables
Mar 26, 2008Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
Integration Services :: Documenting SSIS Packages
Nov 10, 2010What is the best way of documenting SSIS packages?I am not interested in purchasing any software, any general template that covers the key information of any SSIS package that should be defined and documented.
View 5 Replies View RelatedCannot Creat Stored Procedure On Existing Database
May 15, 2008Hi, I already got a database and I need to add another stored procedure on it in order to call it from the SSIS package.
But when I execute the sql file to create the procedure, there is a error message popping out like:
"Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'logit'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists."
And I have tried it on another computer, there is the same error. Could you please tell me how to solve the problem. Thanks very very much.