Important SQL Codes

by 9:35 PM 0 comments
GET ROWCOUNTS FOR ALL TABLES
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM        sysobjects so,
sysindexes si
WHERE so.xtype = 'U'
AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC 

GET REFERENCES OF DATABASE OBJECTS
SELECT  OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name,
     referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
      AND is_ambiguous = 0
  ORDER BY referenced_entity_name desc


SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%TableName%'

DETERMINE WHEN A STORED PROCEDURE WAS CREATED AND LAST ALTERED
select
SPECIFIC_NAME AS procedureName,
SPECIFIC_CATALOG AS databaseName,
CREATED AS createDate,
LAST_ALTERED as lastAlteredDate
from
information_schema.routines
where routine_name = '<Procedure Name Here>'

FIND COLUMN IN ALL TABLES
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ColumnName%'
ORDER BY schema_name, table_name


UDPATE WITH JOIN
UPDATE
    tbl_User
SET
    sShortUserID = sui.sShortUserID
FROM
    tbl_User u
    JOIN
    tbl_ShortUserIDs sui ON u.uSystemUserID=sui.uSystemUserID

select u.uSystemUserID,u.sShortUserID,sui.uSystemUserID,sui.sShortUserID FROM
    tbl_User u
    JOIN
    tbl_ShortUserIDs sui ON u.uSystemUserID=sui.uSystemUserID


REPLACE IN A ROW

UPDATE dbo.tbl_UserLiveStreamPlayback
SET sPlaybackURL = REPLACE(sPlaybackURL,'SourceText','ReplaceByText')


SELECT SP CONTAINING SPECIFIC TEXT
SELECT OBJECT_NAME(id)
    FROM syscomments
    WHERE [text] LIKE '%TextToSearch%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    GROUP BY OBJECT_NAME(id)


TO COUNT NUMBER OF TABLES IN A DB
  SELECT [TABLECOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE='U'

TO COUNT NUMBER OF STORED PROCEDURES IN A DB
  SELECT [SPCOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE='P'


GET LIST OF TABLES NAMES AND THEIR ROW COUNTS
  SELECT [TABLENAME] = OBJ.NAME, [ROWCOUNT] = MAX(SI.ROWS) FROM SYSOBJECTS OBJ,
  SYSINDEXES SI WHERE OBJ.XTYPE = 'U' AND SI.ID = OBJECT_ID(OBJ.NAME)
  GROUP BY OBJ.NAME ORDER BY [ROWCOUNT] DESC


GET COLUMNS NAMES, DATA TYPES, IS NULLABLE OF A PARTICULAR TABLE
  SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE + CASE ISNULL(CHARACTER_MAXIMUM_LENGTH,'')
  WHEN '' THEN '' ELSE ' (' + CAST (CHARACTER_MAXIMUM_LENGTH AS VARCHAR(6)) + ')'
  END AS [DATA TYPE], CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL'
  END AS [NULL OR NOT] FROM INFORMATION_SCHEMA.COLUMNS WHERE
  TABLE_NAME = 'L_SAMPLE' ORDER BY TABLE_NAME, COLUMN_NAME


TO SEE THE DEFINITION OF A SP 
  EXEC SP_HELPTEXT GET_USER_DATA


GET TABLES CREATION AND MODIFIED DATES
  SELECT [TABLENAME] = NAME, CREATE_DATE, MODIFY_DATE FROM SYS.OBJECTS
  WHERE TYPE = 'U' --AND NAME LIKE '%L_%' 
  ORDER BY MODIFY_DATE DESC


GET STORED PROCEDURES CREATION AND MODIFIED DATES
  SELECT [SPNAME] = NAME, CREATE_DATE, MODIFY_DATE FROM SYS.OBJECTS
  WHERE TYPE = 'P' --AND NAME LIKE '%GET%' 
  ORDER BY MODIFY_DATE DESC

GET TABLES THAT ARE NOT USED
select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
    sys.sql_modules
    inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
where t.type = 'U'
and sys.objects.name is null
order by t.name, type_desc, foundin

FIND MOST EXPENSIVE STORED PROCEDURE
SELECT DB_NAME(SQTX.DBID) AS [DBNAME]
,OBJECT_SCHEMA_NAME(SQTX.OBJECTID, DBID) AS [SCHEMA]
,OBJECT_NAME(SQTX.OBJECTID, DBID) AS [STORED PROC]
,MAX(CPLAN.USECOUNTS) [EXEC COUNT]
FROM SYS.DM_EXEC_CACHED_PLANS CPLAN
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CPLAN.PLAN_HANDLE) SQTX
WHERE DB_NAME(SQTX.DBID) IS NOT NULL
AND CPLAN.OBJTYPE = 'PROC'
GROUP BY CPLAN.PLAN_HANDLE
,DB_NAME(SQTX.DBID)
,OBJECT_SCHEMA_NAME(OBJECTID, SQTX.DBID)
,OBJECT_NAME(OBJECTID, SQTX.DBID)
ORDER BY MAX(CPLAN.USECOUNTS) DESC


Ravi Tuvar – IntelliMedia Networks
Software Developer
Cell: (+91) 73837-94530

ravi tuwar

Developer

Cras justo odio, dapibus ac facilisis in, egestas eget quam. Curabitur blandit tempus porttitor. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor.

0 comments:

Post a Comment