How to create a stored procedure on multiple databases

I noticed this particular question asked on one of the SQL forums the other day, however, this same question can apply to any database objects, like how to create a function on multiple databases, how to create a view on multiple databases, how to create a table on multiple databases, etc. If you think about it just for a bit you will realize that in fact the generic question that covers all those particular cases and more is: how to execute a t-sql script against multiple databases.

Well the best, safest and most efficient way for executing or deploying t-sql scripts on multiple databases is to utilize xSQL Software's Script Executor tool. Here is the breakdown of the time you would need to spend to accomplish your task:

• Download and install Script Executor -> 2 minutes max
• Create a database group and add your servers and databases into the group -> 30 seconds per server
• Create one or more script containers and add your t-sql scripts to those containers -> less than 2 minutes depending on where the scripts are and how you might need to organize them
• Map scripts to databases and set execution priorities -> 2 to 3 minutes depending on how complicated your deployment scenario is.
• (optional) Create a deployment package -> 10 seconds (you would create a deployment package if you wish to executed the scripts from a machine where you might not have the Script Executor tool installed)
• (optional) Create a batch file that executes the deployment package created above OR that executes a saved deployment project from the command line -> 2 minutes
• (optional) Create a scheduled task that executes the batch file -> 1 minute

So, in about 10 minutes you will have created an automated job that deploys the scripts you want to the servers and databases you want, when you want! Now whenever you might wish to deploy one or more scripts that you have created to those databases all you need to do is drop the scripts to the folder(s) to which the Script Containers created above are pointing to and you are done - your scheduled task will take care of the rest.

 

Download Script Executor now and see what you have been missing.

Read More

How to get list of tables, number of rows, data and index space

Here is a simple query that returns the complete list of user tables on a SQL Server database - it includes the schema name, table name, the date on which the table was created, the number of rows, the disk space occupied by the data in KB and the disk space occupied by the indexes:

SELECT sschemas.name AS SchemaName
               ,sobjects.name AS TableName
               ,sobjects.create_date AS CreatedOn
               ,sobjects.modify_date AS ModifiedOn
               ,MAX(sstats.row_count) AS NoRows
               ,SUM(CASE
                               WHEN (sstats.index_id < 2)
                                       THEN sstats.in_row_data_page_count + sstats.lob_used_page_count +
                                                   sstats.row_overflow_used_page_count
                                        ELSE 0
                            END) * 8 AS DataSpaceKB
                 ,SUM(CASE
                                 WHEN (sstats.index_id >= 2) THEN sstats.used_page_count
                                 ELSE sstats.in_row_used_page_count - sstats.in_row_data_page_count
                   END) * 8 AS IndexSpaceKB
FROM sys.schemas AS sschemas
            INNER JOIN sys.objects AS sobjects ON sschemas.schema_id = sobjects.schema_id
            INNER JOIN sys.dm_db_partition_stats AS sstats ON sobjects.object_id = sstats.object_id
WHERE sobjects.type = 'U'
GROUP BY sschemas.name, sobjects.name, sobjects.create_date, sobjects.modify_date
ORDER BY sobjects.name

A couple of things you need to know in order to understand this:

• one page is equal to 8K in size 
• index_id values are 0 for the heap, 1 for the clustered index and > 1 for nonclustered indexes
• in_row_data_page_count represents the number of pages used for storing in-row data and it only includes the leaf pages
• lob_used_page_count represents the number of pages used for storing out of row text, n/varchar(max), n/varbinary(max), image, and xml columns.
• row_overflow_used_page_count represents the number of pages for storing row overflow
• in_row_used_page_count represents the total number of pages in use to store in-row data including both leaf and non-leaf pages. Hence, to determine the index space for the clustered index we subtract the in_row_data_page_count from the in_row_used_page_count to get only the non-leaf pages which should be attributed to the index space.

Any questions or comments please leave them here.

If you have not tried our SQL Server comparison download them now http://www.xsql.com/download/sql_server_comparison_bundle/ - quick and easy to install, even easier to use, fast, robust and completely free for SQL Server Express.

Read More

SQL Schema Compare and xSQL Builder new builds available

New builds of SQL Server Schema Compare and xSQL Builder are available for download.

The following fixes are included in both tools: 

• an issue related to conversion of varchar(max) data type to image data type;
• error triggered by foreign keys with the same name created on different tables;
• issue related to dependencies between a full-text index and the unique key/index associated with it;
• small corrections related to the conversion of char, varchar data types to float and real data types;
• issue related to parsing of SQL Server multi-line comments in the object definition;
• issue with the serialization of user-defined data type binding to rules and stand-alone defaults.

Has our SQL Schema Compare (xSQL Object) saved you time? If yes, please consider recommending it on LinkedIn.

Read More

t-sql list of currencies of the World

On February 15, 2022 we released easy lookups by xSQL Software https://lookups.xsql.com/ - you can now get the currencies list and other commonly used lists for free. You can consume those lists directly as JSON/xml services in your JavaScript apps, or download in one of the following, ready to use formats:

• JSON
• XML
• CSV
• SQL Server insert statements
• Oracle insert statements

>>> original article below - links replaced with the new lookup link

The following script (https://lookups.xsql.com/ ) creates a "currencies" table and populates it with the complete ISO 4217 list of international currency codes (edition ISO 4217:2008).
Note that the first two letters of the [AlphabeticCode] in the Currencies table correspond to the [ISO_ALPHA2] code on the Countries table which you can get from https://lookups.xsql.com/, whereas the [NumericCode] in the Currencies table is in most cases the same as the [NumericalCode] in the Countries table.
Please note that the list is current as of the date of this post.

If you have any questions or comments please post them here.

Read More