Script to Get Table Structure Dynamically in Report Format

The below given Script will give you the Table Structure.
For Example : If you want to use table structure in your application dynamically you can run this script.

declare @DCFlag int,@DTFlag int,@DLFlag int,@DNFlag int,@DFFlag int
set @DTFlag=1
set @DLFlag=1
set @DNFlag=1
set @DFFlag=1
select H.COLM_NAME,
UPPER(H.COL_SIZE),
H.IS_NULL_ALLOWED,
DEFAULT_DEFINATION,
'NA' AS DESCRIPTION
FROM
(select object_name(s.object_id)'TAB_NAME',
s.name 'COLM_NAME',
case when @DTFlag=1 then dt.name else 'N/A' end 'DATA_TYPE',
case when @DLFlag=1 then s.max_length else 0 end 'LENGTH',
CASE WHEN dt.name='NUMERIC' AND @DLFlag=1
THEN 'NUMERIC'+'('+ltrim(rtrim(cast(S.PRECISION as char)))+','+ltrim(rtrim(cast(S.SCALE as char)))+')'
WHEN (dt.name='INT' or dt.name='SMALLINT' or dt.name='DATETIME' OR dt.name='TIMESTAMP' or dt.name='SMALLINT' or dt.name='BIGINT')and @DLFlag=1
THEN dt.name
WHEN (dt.name='NCHAR' OR dt.name='NVARCHAR')
THEN dt.name+'('+ltrim(rtrim(cast(s.max_length/2 as char)))+')'
WHEN (dt.name='INT' or dt.name='SMALLINT' or dt.name='DATETIME' OR dt.name='TIMESTAMP' or dt.name='SMALLINT' or dt.name='BIGINT')and @DLFlag=1
THEN dt.name
ELSE dt.name+'('+ltrim(rtrim(cast(s.max_length as char)))+')' END 'COL_SIZE',
case when s.is_nullable=1 and @DNFlag=1
then 'NULL'
when s.is_nullable=0 and @DNFlag=1
then 'NOT NULL'else 'N/A' END 'IS_NULL_ALLOWED',
CASE when (s.default_object_id>0 and @DFFlag=1)
then 'Y'
when @DFFlag=0
then 'N/A'
ELSE 'N' END 'IS_DF_DEFINED',
case when @DFFlag=1 and s.default_object_id>0
then(select definition from sys.default_constraints
where parent_object_id=s.object_id and parent_column_id=s.column_id )
else 'NA' end 'DEFAULT_DEFINATION'
FROM sys.columns s
INNER JOIN sys.types dt
on s.system_type_id=dt.system_type_id
WHERE EXISTS(SELECT * FROM sysobjects where id=s.object_id and xtype='U' )
and dt.name<>'sysname' -- and s.max_length<9
--and (dt.name='char' or dt.name='varchar' )and dt.name<>'sysname'
)H
WHERE H.TAB_NAME='Table_Name'

Query to Find Column(s) Name From All Tables of Database

The following script which will go return all the tables containing specific column along with their schema name.

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 '%EmpName%'
ORDER BY schema_name, table_name

If you want to find all the column name from your database run following script.

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
ORDER BY schema_name, table_name

Note: You can down any condition in WHERE clause to get desired result.

Find the String in all the Tables in a Database for Exact Match or Non Exact Match

SP for find the String in all the Tables in a Database for Exact Match or Non Exact Match

The below given SP will give u the schema name,table name and column name. By which you can find your string.
For Example : - SchemaName TableName ColumnName
dbo employee empname

From this you easily find which table's column have a string that you are finding.


--FindMyData_String 'ram',1

alter PROCEDURE FindMyData_String
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE
( RowId INT IDENTITY(1,1),
SchemaName sysname,
TableName sysname,
ColumnName SysName,
DataType VARCHAR(100),
DataFound BIT
)

INSERT INTO @Temp
( TableName,
SchemaName,
ColumnName,
DataType
)
SELECT C.Table_Name,
C.TABLE_SCHEMA,
C.Column_Name,
C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')

--SELECT * FROM @Temp
--RETURN


DECLARE @i INT
DECLARE @MAX INT
--DECLARE @TableName sysname
--DECLARE @ColumnName sysname
--DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,

@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1

--PRINT @SQLTemplate
--SELECT * FROM @Temp
--RETURN


SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp

WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i


--PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

SET @i = @i + 1
END

SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
GO

Nth Highest Salary or Nth Min/Max Records Script

1) Find 6th Highest Salary Using Top Clause

SELECT TOP 1 salary FROM
(SELECT DISTINCT TOP 6 salary FROM employeeORDER BY salary DESC) a
ORDER BY salary

2) Find 4th Highest Salary Using Min/Max Function

SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 4 Sal FROM TableName ORDER BY Sal DESC)

3) Find Nth Max Number

select top 1 quantity from
(SELECT TOP n-1 quantity FROM
(SELECT DISTINCT TOP n quantity FROM productorder
ORDER BY quantity DESC) as A
ORDER BY quantity desc )as B
order by quantity


4) Find 10th Highest Salary Using Row_Number() Function

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (order by EMP.SAL DESC) AS RANK FROM EMP) v where RANK = 10;

View Table Dependencies Script(Table that depend on / Table on which depend)

OBJECT(TABLE) THAT DEPEND ON OBJECTS:

SELECT OBJECT_NAME(constraint_object_id)'CONS_NAME',
OBJECT_NAME(parent_object_id)'CHILD',
(select name from sys.columns where column_id=parent_column_id and object_id=parent_object_id)'CHILD_col',
OBJECT_NAME(referenced_object_id) 'PARENT',
(select name from sys.columns where column_id=referenced_column_id and object_id=referenced_object_id)'PARENT_COL'
FROM SYS.FOREIGN_KEY_COLUMNS
WHERE referenced_object_id =OBJECT_ID('TABLE NAME')

OBJECT(TABLE) ON WHICH OBJECTS DEPEND

SELECT OBJECT_NAME(constraint_object_id)'CONS_NAME',
OBJECT_NAME(parent_object_id)'CHILD',
(select name from sys.columns where column_id=parent_column_id and object_id=parent_object_id)'CHILD_col',
OBJECT_NAME(referenced_object_id) 'PARENT',
(select name from sys.columns where column_id=referenced_column_id and object_id=referenced_object_id)'PARENT_COL'
FROM SYS.FOREIGN_KEY_COLUMNS
WHERE parent_object_id =OBJECT_ID('TABLE NAME')