Finding out Null valued columns in a database

USE [Database name]

DECLARE @colName VARCHAR(200)
DECLARE @tabName VARCHAR(200)

DECLARE @nullTable  TABLE
(TABLE_NAME  varchar(200),
COLUMN_NAME  varchar(200),
NOTNULLCOUNT  int)

DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT  T.NAME , C.NAME
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE';

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Count AS INT
DECLARE @SQLString nvarchar(500)
SET @SQLString =N'SELECT '''+@tabName+''','''+@colName+''',count(*) FROM dbo.['+@tabName+'] WHERE  ['+@colName+'] IS NOT NULL'
PRINT @SQLString
INSERT @nullTable EXEC (@SQLString)


FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor


SELECT * FROM @nullTable WHERE NOTNULLCOUNT=0 ORDER BY TABLE_NAME,COLUMN_NAME

List all SSRS data souce with connection string

;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;

List all the tables in a database

SELECT * FROM sysobjects WHERE xtype='U'

Here is a list of other object types you can search for as well:

• C: Check constraint
• D: Default constraint
• F: Foreign Key constraint
• L: Log
• P: Stored procedure
• PK: Primary Key constraint
• RF: Replication Filter stored procedure
• S: System table
• TR: Trigger
• U: User table
• UQ: Unique constraint
• V: View
• X: Extended stored procedure

In SQL Server 2012, you can use

SELECT * FROM information_schema.tables where table_type='BASE TABLE'

You can also use

select * from sys.tables

List of all SSRS reports name and their datasource

SELECT
    DS.Name AS DatasourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.Catalog AS C
        INNER JOIN
    ReportServer.dbo.Users AS CU
        ON C.CreatedByID = CU.UserID
        INNER JOIN
    ReportServer.dbo.Users AS MU
        ON C.ModifiedByID = MU.UserID
        LEFT OUTER JOIN
    ReportServer.dbo.SecData AS SD
        ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
        INNER JOIN
    ReportServer.dbo.DataSource AS DS
        ON C.ItemID = DS.ItemID
WHERE
    DS.Name IS NOT NULL
ORDER BY
    DS.Name;

List all the jobs of sql server database

SELECT name AS [Job Name],CASE WHEN enabled=1 THEN 'YES' ELSE 'NO' END AS ENABLED,
description AS Description
FROM msdb.dbo.sysjobs
ORDER BY enabled,description

List all the function name for a database

connect to any database.
Use [Database name]


SELECT name AS function_name,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'

List MSSQL Database size

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN
   (
   SELECT database_id, SUM(size) RowSize FROM sys.master_files
   WHERE type = 0
   GROUP BY database_id, type
   ) mfrows
   ON mfrows.database_id = db.database_id
   LEFT JOIN
  (
  SELECT database_id, SUM(size) LogSize FROM sys.master_files
  WHERE type = 1
  GROUP BY database_id, type
   ) mflog
  ON mflog.database_id = db.database_id
  LEFT JOIN
  (
  SELECT database_id, SUM(size) StreamSize
  FROM sys.master_files
  WHERE type = 2
  GROUP BY database_id, type
  ) mfstream
  ON mfstream.database_id = db.database_id
  LEFT JOIN
  (
   SELECT database_id, SUM(size) TextIndexSize
   FROM sys.master_files
   WHERE type = 4
   GROUP BY database_id, type
   ) mftext
   ON mftext.database_id = db.database_id