01495 775 190

Seren Software Services provides
Professional Consultancy and Development Services

About Us
Who We Are
and What We Do
Development Services
Software Coding Services for
Databases, Apps and The Internet
Consultancy Services
Professional Consultancy for Software
Project Management and IT needs
Software
Apps, Games
And Addons
Contact Us
Ask A Question Or
Get A Free Quote
Web Designer?

Need Complex Coding?
System Intergration?
Module/Plugin Required?
Modification to CMS?
Database Implementation?

Find Out More...

Home Technical Blog
Search Stored Procedures
Written by Jon Hibbins   
Friday, 05 December 2008 09:22
Ever had the need to search stored procedures to find redundant tables or text that needs replacing or re-factoring ?

Here's a script to help (Sorry for the cursor, I'm sad with myself for using it :-( )

-- Search string
DECLARE @SearchString nvarchar(50)
SET @SearchString = 'Address'

-- Required Declares
DECLARE @getdbname sysname
DECLARE @sqlstm nvarchar(1000)
DECLARE SeachCursor cursor for
-- Get All The Names into the SeachCursor
SELECT '['+name+']' FROM [master].[dbo].[sysdatabases] ORDER BY name
OPEN SeachCursor
-- Add the Search Pattern
SET @SearchString = '%' + @SearchString + '%'
--Get the first Name
FETCH NEXT FROM SeachCursor into @getdbname
WHILE @@FETCH_STATUS=0
    BEGIN
    --set the statement to define the search condition, with variables
    SET @sqlstm = '
    SELECT [SP].[Specific_Catalog] AS [Database_Name], [SP].[Routine_Name] AS [Stored Procedure Name],[SP].[Routine_Definition] AS [Routine_Definition]
    FROM '+ @getdbname+'.[Information_Schema].[Routines] AS [SP]
    WHERE PatIndex('+''''+@SearchString+''''+', [Routine_Definition]) > 0'
    --Execute the Query
    EXEC (@sqlstm)
    FETCH NEXT FROM SeachCursor into @getdbname
    END
--Close the Cursor and Deallocate it from memory
CLOSE SeachCursor
DEALLOCATE SeachCursor

 
SQL 2008 Enable Changes To Tables
Written by Jon Hibbins   
Wednesday, 08 October 2008 00:00

If you get the error in the SQL 2008 Management Studio :

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

You can enable changes inside the Microsoft SQL Server Management Studio by going to 'Tools|Options|Designers' then unchecking the 'Prevent saving changes that require table re-creation' option
 
Autostart Zend Core on Ubuntu
User Rating: / 2
PoorBest 
Written by Jon Hibbins   
Thursday, 18 September 2008 00:00
This is the 2nd time I have had to search for starting up Zend Core automatically on Ubuntu Workstation, so here is the answer: First change the permissions of /etc/rc.local by opening a terminal window (Applications|Accessories|Terminal) and enter the following command:
sudo chmod 777 /etc/rc.local
Then open this file with the text editor (Applications|Accessories|Text Editor) and put the following text before the exit 0 command
cd /usr/local/Zend/Core/mysql && ./bin/safe_mysqld & /usr/local/Zend/apache2/bin/apachectl start &
Zend Core, PHP and MySQL should now all start automaticaly at boot time.
 
TSQL Count Number Of Stored Procedures, Views, Tables or Functions
User Rating: / 2
PoorBest 
Written by Jon Hibbins   
Thursday, 04 September 2008 00:00
You can use TSQL to Count Number Of Stored Procedures, Views, Tables or Functions in a Database by using the Database INFORMATION_SCHEMA view
/* Count Number Of Tables In A Database */
SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
/* Count Number Of Views In A Database */
SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS
/* Count Number Of Stored Procedures In A Database */
SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
/* Count Number Of Functions In A Database */
SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'
The same methodology can be used to query for information :

/* Select Table Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
/* Select View Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.VIEWS
/* Select Stored Procedure Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
/* Select Function Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

 
SQL Server - TSQL DISTINCT or GROUP BY?
User Rating: / 2
PoorBest 
Written by Jon Hibbins   
Wednesday, 03 September 2008 00:00

DISTINCT v GROUP BY

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be very simular or the same, if you only need to remove the dulicates then DISTINCT is easier to understand in the query, if you have a more complex query it may be worth running both and studdying the execution plan to see if your specific example wins
 
«StartPrev123NextEnd»

Database Development
Let our highly experienced team of professionals help you optimise your database system’s performance and maximise your SQL Server database system investments.

We customise our service according to your requirements and we offer support services for all versions of Microsoft SQL Server, including SQL Server 2000, 2005 and 2008. We can also help with you MySQL needs!

Microsoft SQL Server Logo   Microsoft SQL Server Logo

Read More Of This Article >> 
Project Management

Planning, organising, and managing resources to bring about the successful completion of specific project deliverable is a complex task.

Seren employee experianced proffessionals in Microsoft Project and Microsoft Project Server to help you deliver quality, on time and within budget.

Read More Of This Article >> 

United Kingdom, South Wales, Cardiff, Newport, Bristol, Monmouthshire, Gwent, Glamorgan, Brecon/Brecon Beacons, Radnor, Carmarthen, Monmouth,Raglan, Crickhowell , Usk, Ebbw Vale, Brynmawr, Hereford, Merthyr Tydfil, Tredegar, Hay-on-Wye, Cwmbran, Pontypool, Penarth, Swansea, Port Talbot, Neath, Aberdare, Bridgend, Caerphilly, Risca, Pontypridd, Chepstow, Gloucester, Abergavenny.