Quick Queries for SQL

/************************** Check list of tables in DB ****************************/

SELECT ‘[‘+TABLE_SCHEMA+’].[‘+TABLE_NAME+’]’ FROM <DB_Name>.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’
ORDER BY TABLE_SCHEMA

GO

/******************************* Check Table and SP *********************************/

SP_HELP ‘DBO.TableName’ — Table_Name

GO

SP_HELPTEXT ‘DBO.SPName’ — SP_Name

GO

SP_HELPDB DBName — DB_Name

GO

SP_SPACEUSED — Total free space including Transaction log free space

GO

DBCC SQLPERF(logspace) –Log Space Used for all DB

GO

SELECT    DB_NAME() AS DBNAME

        ,[FILE_SIZE_MB] = CONVERT(DECIMAL(12,2)

        ,ROUND(A.SIZE/128.000,2))

        ,[SPACE_USED_MB] = CONVERT(DECIMAL(12,2)

        ,ROUND(FILEPROPERTY(A.NAME,’SPACEUSED’)/128.000,2))

        ,[FREE_SPACE_MB] = CONVERT(DECIMAL(12,2)

        ,ROUND((A.SIZE-FILEPROPERTY(A.NAME,’SPACEUSED’))/128.000,2))

        ,[FREE_SPACE_%] = CONVERT(DECIMAL(12,2)

        ,(CONVERT(DECIMAL(12,2),ROUND((A.SIZE-FILEPROPERTY(A.NAME,’SPACEUSED’))/128.000,2)) / CONVERT(DECIMAL(12,2),ROUND(A.SIZE/128.000,2)) * 100))

        ,a.NAME,a.FILENAME

        ,SUBSTRING(A.FILENAME, 1, 1) DRIVE

FROM     DBO.SYSFILES a

ORDER BY Drive, [Name]

GO

/************************ Check currently running queries in SQL **********************/

SELECT sqltext.TEXT

        ,req.session_id

        ,req.status

        ,req.command

        ,req.cpu_time

        ,req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

/******************* Get Column Names and its type in SQL *********************/

SELECT

    OBJECT_NAME(c.object_id) AS [Table Name]

,c.name AS [Column Name]

,t.Name AS [Data Type]

,c.max_length AS [Max Length]

,c.precision AS [Precision]

,c.scale AS [Scale]

    ,CASE WHEN c.is_nullable=0 THEN ‘No’ ELSE ‘Yes’ END AS [Nullable Column]

    ,CASE WHEN I.is_primary_key IS NULL THEN ‘No’ ELSE ‘Yes’ END AS [Primary Key Column]

    ,CASE WHEN C.is_identity=1 THEN ‘Yes’ ELSE ‘No’ END AS [Identity Column]

FROM

sys.columns c

INNER JOIN

sys.types t ON c.user_type_id = t.user_type_id

LEFT OUTER JOIN

sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

LEFT OUTER JOIN

sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

WHERE

c.object_id = OBJECT_ID(‘HEDIS_V9_Temp1230.CS.CUSTOMER’)

/*********************** Check Whether Column exists in DB ************************/

SELECT    TABLE_CATALOG AS [DB Name]

        ,UPPER(TABLE_SCHEMA)+’.’+TABLE_NAME AS [Table Name]

        ,COLUMN_NAME

        ,IS_NULLABLE–,*

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME=’CustomerId’

/************************** Check Whether Table exists in DB ***************************/

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = N’TableName’ –AND TABLE_SCHEMA = ‘SchemaName’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s