Saturday, July 18, 2015

complete information from database

select a.name [Table],b.name [Attribute],c.name [DataType],b.isnullable [Allow Nulls?],CASE WHEN
d.name is null THEN 0 ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],CASE WHEN e.parent_object_id
is null THEN '-' ELSE g.name  END [Ref Table],
CASE WHEN h.value is null THEN '-' ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype = c.xtype
left join (SELECT  so.id,sc.colid,sc.name
      FROM    syscolumns sc
      JOIN sysobjects so ON so.id = sc.id
      JOIN sysindexkeys si ON so.id = si.id
                    and sc.colid = si.colid
      WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id  
left join sys.objects as g on e.referenced_object_id = g.object_id
left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id
where a.type = 'U' order by a.name

Sunday, July 5, 2015

EXTENDED METHODS IN jQuery


/******* EXTENDED METHODS IN jQuery ***************/

/*Mehod1: this will remove any inline css attribute.  */
(function ($) {
    $.fn.removeStyle = function (style) {
        var search = new RegExp(style + '[^;]+;?', 'g');

        return this.each(function () {
            $(this).attr('style', function (i, style) {
                return style.replace(search, '');
            });
        });
    };
}(jQuery));

/*Mehod2: this will change existing class with red. pre-requites class name should be postfixed with _red with existing class name e.g. Existing class name .top_lt  so new name for redclass will be .top_lt_red
It will only work when setRedClass will set to 1 e.g. setRedClass(1)-> All class will renamed with redclass setRedClass(0)-> nothing will happen.
*/
(function ($) {
    $.fn.setRedClass = function (baddebtValue) {
        if (baddebtValue == "1") {
            $("[setBDColor='yes']").removeStyle('background');
            $("[setBDColor='yes']").css("background", 'url(../../images/box_hdg_bg_red.gif) left top repeat-x;');
            $("[setBDColor='redclass']").each(function (a, b, c) {
                var className = $(this).attr('class').trim();
                $(this).removeClass(className);
                className = className + '_red';
                $(this).addClass(className);
            });
        }
    };
}(jQuery));
//How to call
$('#abc').find('a').removeStyle('color');
Method2
 $.fn.setRedClass($("[id$=hfIsBaddebt").val());

Wednesday, July 1, 2015

How to create scripts of stored procedure modified on specific period

SET NOCOUNT ON;
DECLARE @HelpText TABLE
(
    Val NVARCHAR(MAX)
);

DECLARE @sp_names TABLE
(
    ID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(128)
);

DECLARE @sp_count INT,
        @count INT = 0,
        @sp_name NVARCHAR(128),
        @text NVARCHAR(MAX);

INSERT  @sp_names
SELECT  name
FROM    sys.Procedures  where name like '%salary%'  and modify_date between '2015-06-22 12:17:03.860' and '2015-07-01 11:38:35.187'

SET @sp_count = (SELECT COUNT(1) FROM sys.Procedures where name like '%salary%'  and modify_date between '2015-06-22 12:17:03.860' and '2015-07-01 11:38:35.187');

WHILE (@sp_count > @count)
BEGIN
    SET @count = @count + 1;
    SET @text = N'';

    SET @sp_name = (SELECT  name
                    FROM    @sp_names
                    WHERE   ID = @count);

    INSERT INTO @HelpText
    EXEC sp_HelpText @sp_name;

    SELECT  @text = COALESCE(@text + ' ' + Val, Val)
    FROM    @HelpText;
  declare @dropIfExists varchar(250) = '  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@sp_name+']'') AND type in (N''P'', N''PC''))
     DROP PROCEDURE [dbo].['+@sp_name+'] '
     PRINT 'GO'
     print @dropIfExists
     PRINT 'GO'
print @text
    DELETE FROM @HelpText;

 
END