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
Email: ravituvar@yahoo.com
0 comments:
Post a Comment