Sp_RefreshView

Problem:

Sometimes metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Solution:

Since the metadata for the referenced table columns is persisted in the database, changes to the referenced objects will not get reflected until the view is refreshed using the system stored procedure sp_refreshview.

Syntax:

USE database_name

EXEC sp_refreshview [view_name]

GO

The following script refreshes the metadata for all the views that have dependencies on a changed object

SELECT DISTINCT 'EXEC sp_refreshview ''' + a.name + ''''

FROM sys.objects a INNER JOIN sys.sql_dependencies b

ON a.object_id = b.object_id

WHERE type = 'V'

AND b.referenced_major_id = object_id('tablename')

SQL Server Import and Export - Issue

When we import data from excel sheet to SQL Server tables, we come across some issues related to data type mismatch and constraints. Recently I have faced such issue with the error message saying,

Error:

"The value violated the integrity constraints for the column.” (SQL Server Import and Export Wizard)

Cause:

This error will come if the source table has not NULL column and the excel sheet column with NULL values.

In my case there is no NULL values in the excel sheets. The field has complete data, but it contains the data with string, integer, Boolean, Float and number data types

Workaround:

Sort the field by the type of the data it contains. For e.g. here the column 2 values have different types of data

Column 1	Column 2
FLOAT	2
STRING	MONTH
NUMBER	0
STRING	B01
BOOLEAN	TRUE
DATETIME	4/20/2011
NUMBER	3411

Sorting them in an order to make the data with the text come up on the top will solve the problem.  If you have thousands of records with the values of different format in a single column, you can just filter the values by the format and copy the results into a new sheet then try importing into the SQL server table. It will work

Finding the spaces used by all the tables

To get the size of all the tables in a database, create a following stored procedure using the database you need,

CREATE PROCEDURE GetAllTableSizes

AS

/*

    Obtains spaced used data for ALL user tables in the database

*/

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR

FOR

select [name]

from dbo.sysobjects

where  OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable

(

    tableName varchar(100),

    numberofRows varchar(100),

    reservedSize varchar(50),

    dataSize varchar(50),

    indexSize varchar(50),

    unusedSize varchar(50)

)

--Open the cursor

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

    --Dump the results of the sp_spaceused query to the temp table

    INSERT  #TempTable

        EXEC sp_spaceused @TableName

    --Get the next table name

    FETCH NEXT FROM tableCursor INTO @TableName

END

--Get rid of the cursor

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the results

SELECT *

FROM #TempTable

--Final cleanup!

DROP TABLE #TempTable

GO

EXEC GetAllTableSizes

Once you created the procedure just execute it and you will get the results as in the picture below.

Number of rows: The total number of rows that each table has in a database.

Reserved size:  The total amount of space allocated by the table

Data size:  Total amount of space used by data.

Index size:  Total amount of space used by indexes.

Unused space:  Total amount of space reserved for the objects, yet to be used.

database_size will always be larger than the sum of reserved and unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

To find the log space, use the DBCC command,

  DBCC SQLPERF ( LOGSPACE )

It will tell you the log size in MB and log space used in %

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Query to get the scheduled jobs detail

Few days before, I was asked a query by one of my friends to retrieve scheduled job details such as job name and the mail id to which the job has configured.  
It is very easy to get the details using sysjobs, sysoperators and syscategories system tables. Here is the script I created to list the jobs name and email id with some extra columns such as job owner, category, enabled and email notification.

          

  SELECT   [Job Name]           = j.[name]                  ,

           [Owner]              = SUSER_SNAME(j.[owner_sid]),

           [Category]           = c.[name         ]                  ,

           [Email id]           = o.[email_address]                  ,

           [Enabled]            = j.[enabled      ]                  ,

           [Email Notification] =

           CASE j.[notify_level_email]

                    WHEN 0

                    THEN 'Never'

                    WHEN 1

                    THEN 'When the job succeeds'

                    WHEN 2

                    THEN 'When the job fails'

                    WHEN 3

                    THEN 'When the job completes (regardless of the job outcome)'

                    ELSE 'UNKNOWN'

           END

  FROM     msdb.dbo.[sysjobs] j

           LEFT OUTER JOIN msdb.dbo.[sysoperators] o

           ON       j.[notify_email_operator_id] = o.[id]

           LEFT OUTER JOIN msdb.dbo.[syscategories] C

           ON       j.[category_id] = c.[category_id]

  WHERE    j.[enabled]              = 1

  ORDER BY j.[name]

Using this script you can retrieve the details for all the enabled jobs. If you want to get disabled jobs too, just eliminate the ‘where’ clause in the script and try.:-)
Then the output will looks like this,