Tag Archives: Scripts

I-Tech SQL Server Script Series – Listing Clustered Indexes on All Tables

A good general rule for creating tables is to always have a clustered index. The clustered index defines how data is physically ordered on the disk – and that’s why there can only be one clustered index on a table (a table without a clustered index is a HEAP). Even though it’s a good rule to have a clustered index there are common scenarios when you won’t have one on a table. A typical example is a load or import table, but even then it may have a clustered index applied when the load is complete.

When investigating a database it is helpful to understand the clustered indexes. The chosen clustered index can have a large impact on performance. Understanding the clustered indexes is especially crucial since every non-clustered index on a table relies on the clustered index.

SQL Server stores object information, including indexes, in system views. Table information is available in sys.objects and the associated indexes are in sys.indexes. This script list all of the tables in the current database and their associated clustered index. If the table doesn’t have a clustered index it will have NULL for the IndexName. You can use SSMS to look at indexes individually, but listing them via SQL makes it easy to see all of them at once.

SQL Script

SELECT
	@@SERVERNAME		ServerName
	,DB_NAME()			DatabaseName
	,ss.name			SchemaName
	,so.name			TableName
	,si.name			IndexName
	,sc.name			ColumnName
	,st.name			ColumnType
	,CASE
		WHEN st.is_user_defined = 0 THEN st.name
		WHEN st.is_table_type	= 1	THEN AT.name
		WHEN st.is_user_defined = 1	THEN st2.name
	END ColumnBaseType
	,CASE 
		WHEN sc.max_length = -1 THEN 0
		WHEN st.name IN ('nchar','nvarchar') THEN sc.max_length	/ 2
		WHEN st2.name IN ('nchar','nvarchar') THEN sc.max_length	/ 2
		ELSE sc.max_length
	END	ColumnMaxLength
FROM sys.objects so
	INNER JOIN sys.schemas SS
		ON SO.schema_id = SS.schema_id
	LEFT JOIN sys.indexes si
		ON si.object_id = so.object_id
		AND si.type = 1	--clustered index
	LEFT JOIN sys.index_columns sic
		ON si.object_id = sic.object_id
		AND si.index_id = sic.index_id
	LEFT JOIN sys.columns sc
		ON sic.object_id = sc.object_id
		AND sic.column_id = sc.column_id
	LEFT JOIN sys.types st
		ON sc.user_type_id = st.user_type_id
	LEFT JOIN sys.types st2
		ON st.system_type_id		= st2.system_type_id 
		AND st2.system_type_id		= st2.user_type_id
		AND st.is_user_defined		= 1
	LEFT JOIN sys.assembly_types AT
		ON ST.system_type_id	= AT.system_type_id
		AND ST.user_type_id		= AT.user_type_id
WHERE so.type = 'u'
ORDER BY so.name
	,sic.Index_Column_ID

Script Download

The script is located here – INDEXES.Clustered.Indexes

It is also located on our script page – I-Tech SQL Server Scripts

I-Tech SQL Server Script Series – Generating BCP Commands

BCP (bulk copy program) is a command-line tool provided with SQL Server for quickly loading and extracting data. BCP extracts or loads data to / from a single table (or from a query) and is very much like a table-level backup file. Not as many developers are aware of BCP since DTS and now SSIS have become available but it is still very useful for moving data around quickly. It’s great for moving files between environments without making a backup of the whole database and it’s also a good safety net before you make a major change to the data in a table. I use it frequently in development environments and I’ve received data-loads from vendors as BCP files because of their ease-of-use and speed of loaded and extracting.

BCP is very flexible and fast, but since it is a command-line tool it has a minimal interface. This can be intimidating to new SQL developers and it can also be tedious if you need to extract all the tables in a database. That’s where this script comes in – it generates the BCP statements that you can put into the command line. Just set the variables at the top of the script and it’s ready to go. Run the script from the database you are targeting. When you run the generated commands it creates or loads the BCP files from the current directory. You can easily add a specific directory to the script if that’s what you need.

SQL Script

SELECT 
    @SERVER          = @@SERVERNAME
    ,@EXTENSION      = 'BCP'
    ,@OPTIONS        = '-n -k -E'
    ,@SCHEMA         = NULL
    ,@TRUSTED        = 1
    ,@USER           = ''
    ,@PASSWORD       = ''
    ,@TYPE           = 'BOTH'	--IN,OUT,BOTH
    ,@INCLUDEDATE    = 1

You can see the options listed above. The -n saves / loads the file in native format (binary). This limits how you can load the file but you don’t need a separate format file with this option. The script also has -k, keep NULL values and -E, keep identity values. Change any of the values needed.

Output

Running the script gives you something like this:

bcp [AdventureWorks2012].[Person].[Address] OUT AdventureWorks2012.Person.Address_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[AddressType] OUT AdventureWorks2012.Person.AddressType_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[dbo].[AWBuildVersion] OUT AdventureWorks2012.dbo.AWBuildVersion_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[BillOfMaterials] OUT AdventureWorks2012.Production.BillOfMaterials_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[BusinessEntity] OUT AdventureWorks2012.Person.BusinessEntity_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[BusinessEntityAddress] OUT AdventureWorks2012.Person.BusinessEntityAddress_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[BusinessEntityContact] OUT AdventureWorks2012.Person.BusinessEntityContact_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[ContactType] OUT AdventureWorks2012.Person.ContactType_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Person].[CountryRegion] OUT AdventureWorks2012.Person.CountryRegion_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[CountryRegionCurrency] OUT AdventureWorks2012.Sales.CountryRegionCurrency_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[CreditCard] OUT AdventureWorks2012.Sales.CreditCard_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[Culture] OUT AdventureWorks2012.Production.Culture_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[Currency] OUT AdventureWorks2012.Sales.Currency_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[CurrencyRate] OUT AdventureWorks2012.Sales.CurrencyRate_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Sales].[Customer] OUT AdventureWorks2012.Sales.Customer_20140804.BCP -n -k -E -T  -S .
...
...
...
bcp [AdventureWorks2012].[Production].[UnitMeasure] OUT AdventureWorks2012.Production.UnitMeasure_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Purchasing].[Vendor] OUT AdventureWorks2012.Purchasing.Vendor_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[WorkOrder] OUT AdventureWorks2012.Production.WorkOrder_20140804.BCP -n -k -E -T  -S .
bcp [AdventureWorks2012].[Production].[WorkOrderRouting] OUT AdventureWorks2012.Production.WorkOrderRouting_20140804.BCP -n -k -E -T  -S .

Results

Running this is very fast. Paste this into a command / batch file or put it directly into a command prompt after changing to the target directory. Some sample output:

C:\Temp\AdventureWorks>bcp [AdventureWorks2012].[Production].[WorkOrderRouting]
OUT AdventureWorks2012.Production.WorkOrderRouting_20140804.BCP -n -k -E -T  -S
.

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
...
...
...
1000 rows successfully bulk-copied to host-file. Total received: 65000
1000 rows successfully bulk-copied to host-file. Total received: 66000
1000 rows successfully bulk-copied to host-file. Total received: 67000

67131 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1092   Average : (61475.27 rows per sec.)

Auto-generating the script makes using BCP even easier. If you need to save space you can zip / gzip the files and get decent compression depending on your database. I’ve seen 5:1 or 10:1 compression, but this varies based on the type of data and your specific tables.

Script
The script is located here – ADMIN.BCP.Create.In.Out

It is also located on our script page – I-Tech SQL Server Scripts

Changing SQL Server Database Owners

In a production SQL Server environment the database owner is usually carefully maintained and consistent. This isn’t always the case on development servers or on servers that you may inherit from other organizations. This script was written originally for a development environment with multiple database owners – one of the developers left the project and their user account needed to be removed.

If you just need to change a single database owner use the SQL command (change the database name and owner of course):

ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2012] TO [sa]

If you need to change the owner on multiple databases or if you just want to see the current owners on the databases you can use the script referenced here – ADMIN.Database.Owners.Change.

Check out the SQL Server BOL entry on ALTER AUTHORIZATION to see the security requirements and other restrictions.

Be sure to check out our other SQL Server scripts here – I-Tech SQL Server Scripts.