Top 8 Useful and Important Scripts in SQL - Part 1

Next Post
By Admin at 16 Mar 2010, 17:58 PM
  • Over the last few months, we have been writing articles to try to assist you in your SQL Server ventures. We have decided to compile a list of our top 8 useful and important scripts of the last couple of months. Please feel free to use this as a resource to improve your productivity. If you have other useful scripts that you want to share, please feel free to post them in the comments.

    1. Get Column and Row count SQL Server

    This example counts the number of columns and rows of all the tables in the current database.

    Create temporary table to store rows and columns count.

    1. GO
    2. CREATETABLE #TableInfo (
    3. tablename sysname ,
    4. rowsINT,
    5. sizeVARCHAR(50),
    6. datasize VARCHAR(50),
    7. indexsize VARCHAR(50),
    8. unusedsize VARCHAR(50))

    The system stored procedure “'sp_spaceused” results in the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database or displays the disk space reserved and used by the whole database. The “sp_msforeachtable” storedprocedure is used to process a single T-SQL command, or a number of different T-SQL commands, against every table in a given database. Insert the records in to the temporary table “#TableInfo”.

    2. INSERT #TableInfo
    3. EXEC sp_msforeachtable 'sp_spaceused "?"'

    Select the records from #TableInfo table.

    1. SELECT a.tablename as [TableName],
    2. a.rows [RowsCount],
    3. COUNT(*) AS [Columns Count],
    4. a.datasize [Data Size]
    5. FROM #TableInfo a
    6. INNERJOIN information_schema.columns b ON a.tablename
    7. collate database_default= b.table_name collate database_default
    8. GROUPBY a.tablename, a.rows, a.datasize
    9. ORDERBYCAST(REPLACE(a.datasize, ' KB', '') ASinteger) DESC
    10. DROPTABLE #TableInfo

    2. Generate Insert Script in SQL Server

    The below script will generate insert statements for the given table.

    Consider the following table:

    1. Createtable tblUser(
    2. [intUserID_pk] [bigint] IDENTITY(1,1) NOTNULL,
    3. [chvUserName] [varchar](200) NULL,
    4. [chvPassword] [varchar](50) NULL,
    5. [chvEmail] [varchar](100) NULL)

    Insert records:

    1. INSERTINTO tblUser ([chvUserName],[chvPassword],[chvEmail]) VALUES('XXX','PWD','A@A.COM')
    2. INSERTINTO tblUser ([chvUserName],[chvPassword],[chvEmail]) VALUES('YYYY','PYYY','B@B.COM')
    3. INSERTINTO tblUser ([chvUserName],[chvPassword],[chvEmail]) VALUES('ZZZZ','ZZPWD','C@C.COM')
    4. INSERTINTO [tblUser] ([chvUserName],[chvPassword],[chvEmail]) VALUES ('AAAA','SSSS','S@S.COM');

    Use the following query to generate an insert script:

    1. SELECT 'INSERTINTO [tblUser] ([chvUserName],[chvPassword],[chvEmail])
    2. VALUES ('''+ [chvUserName] + ''','''+ [chvPassword] + ''','''+ [chvEmail] + ''');' FROM tblUser

    The result set is:


    3. Search text in a stored procedure

    Searching particular text or keyword in all stored procedures will create a problem. Here is a useful script which can be used to perform the task easily.

    1. SELECT OBJECT_NAME(id) FROM syscomments
    2. WHERE [text] LIKE'%tblProduct%'
    3. AND OBJECTPROPERTY(id, 'IsProcedure') = 1

    4. Reduce SQL Database Size

    Following command shrinks the size of the data and log files in the specified database.

    1. Use master
    2. dump tran Databasename with no_log
    3. Use Databasename
    4. dbcc shrinkdatabase (Databasename,4,truncateonly)

    Replace “Databasename” with the name of the database you would like to shrink. Note that the above script only works in SQL Server 2005 and earlier versions of SQL Server.



    Post a comment

    Please correct the following: